how to use multiple substring in same select sql query

Question

0.00/5 (No votes)

See more:

hi
i want to use multiple substring in select query for single column.
so for exp. i have value as 221415525
i want to make it

22:14:155

can i use substring in select as
 SELECT ' ' + substring('221415525', 1, 2)
+ ':' + substring('143825', 3, 2)
+ ':' + substring('143825', 5, 2), name from mytable

Posted 22-Mar-15 20:58pm

maulikshah1990

Add a Solution

Comments

Saral S Stalin 23-Mar-15 3:01am

Yes, you can

SELECT ' ' + substring('221415525', 1, 2)
+ ':' + substring('221415525', 3, 2)
+ ':' + substring('221415525', 5, 2)

Should get you desired result. Let me know if you are expecting something else.

maulikshah1990 23-Mar-15 3:09am

i have value as float 1.09796836411745 , which i convert into time by using

REPLACE(CONVERT(CHAR(20),CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime),114), ':', ''),

and i get result as 221415525 ..
then i need to make it 22:14:15525

how to do this..

Saral S Stalin 23-Mar-15 3:18am

What is the basis of conversion of 1.09 as 22:14? What is the unit of 1.09? Days/Hour/Seconds

maulikshah1990 23-Mar-15 3:20am

i did not understand . can u explain more simply.
as i have used CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime ,114 , it gives value 221415525

Saral S Stalin 23-Mar-15 3:23am

CCAGDT.CONVERSATION_TIME what are you storing in this? Hours/Days?

maulikshah1990 23-Mar-15 3:26am

i am storing float value in CCAGDT.CONVERSATION_TIMe

which i want to convert into time and display like 22:26:11111

Saral S Stalin 23-Mar-15 3:28am

I am not asking about the data type. What you actually store in it? Lets say a job runs for 10 minutes. I can populate this column as 600 seconds, 10 min, 0.167 hours and so on. Knowing the data type alone is not sufficient to solve your problem

Maciej Los 23-Mar-15 3:07am

Does 221415525 is numeric value or string?

maulikshah1990 23-Mar-15 3:18am

i have value as float 1.09796836411745 , which i convert into time by using

REPLACE(CONVERT(CHAR(20),CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime),114), ':', ''),

and i get result as 221415525 ..
then i need to make it 22:14:15525

how to do this..

maulikshah1990 24-Mar-15 1:44am

i have value as float 1.09796836411745 , which i convert into time by using

REPLACE(CONVERT(CHAR(20),CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime),114), ':', ''),

and i get result as 221415525 ..
then i need to make it 22:14:15525

how to do this..

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Answer 1 · 2015-03-22T23:28:00

Solution 1

SQL

DECLARE @STR AS NVARCHAR(MAX) = '221415525'

SELECT CONVERT(DATETIME, SUBSTRING(@STR, 1, 2) + ':' + SUBSTRING(@STR, 3, 2) + ':' + SUBSTRING(@STR, 5, 2) + ':' + SUBSTRING(@STR, 7, 2), 114)

Posted 22-Mar-15 23:28pm

Kornfeld Eliyahu Peter

Comments

maulikshah1990 23-Mar-15 5:36am

thanks for reply.. but time value 2214515525 is generated from float value 1.0925141425 from database ..

the time value is generated by REPLACE(CONVERT(CHAR(20),CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime),114), ':', ''),

CCAGDT.Conversation_time is table field which has value float 1.0952525 , and i cast it to datetime .. so if i dont use replace, it says

[Error converting data type varchar to float.]

and my query should be that it selects one column which will have value

select (substring(1)+':'+substring(2)+':'+substring(3)) , name from tablename
'

SELECT ' ' + substring('221415525', 1, 2)
+ ':' + substring('143825', 3, 2)
+ ':' + substring('143825', 5, 2), name from mytable

I hope it is clear now...

maulikshah1990 24-Mar-15 1:44am

thanks for reply.. but time value 2214515525 is generated from float value 1.0925141425 from database ..

the time value is generated by REPLACE(CONVERT(CHAR(20),CAST(SUM(CCAGDT.CONVERSATION_TIME) AS datetime),114), ':', ''),

CCAGDT.Conversation_time is table field which has value float 1.0952525 , and i cast it to datetime .. so if i dont use replace, it says

[Error converting data type varchar to float.]

and my query should be that it selects one column which will have value

select (substring(1)+':'+substring(2)+':'+substring(3)) , name from tablename
'

SELECT ' ' + substring('221415525', 1, 2)
+ ':' + substring('143825', 3, 2)
+ ':' + substring('143825', 5, 2), name from mytable

I hope it is clear now...

maulikshah1990 · Answer 2 · 2015-04-29T01:58:00

Solution 2

I found answer
ie.

CONVERT(TIME(0),CONVERT(datetime,1.09796836411745)),

this gives output as 02:21:04

Posted 29-Apr-15 1:58am

maulikshah1990