CODEWITHSUNDEEP
sqlsql-server
ic3man7019
ic3man7019

Reputation: 711

How can I convert a varchar to a datetime format of hh:mm:ss?

I have the following results returned from a query: 

2030
1200
1400
1545

Is there a way to convert these to times in the format hh:mm:ss in SQL (SQL Server)?

For example, the above entries would be converted as follows: 

2030 = 20:30:00
1200 = 12:00:00
1400 = 14:00:00
1545 = 15:45:00

Any help is greatly appreciated.

Upvotes: 1

Views: 1157

Answers (3)

Andrea
Andrea

Reputation: 12395

Use FORMAT:

select format(2030 * 100,'##:##:##')

More info about FORMAT here.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You need stuff() :

select cast(stuff(2030, 3, 0, ':') as time(0))

If your query returning string numbers then you can use :

. . .
where charindex ('.', n) = 0;

Upvotes: 7

apomene
apomene

Reputation: 14389

You could create a function like:

CREATE FUNCTION ToTime(@myInput nvarchar(4))  
RETURNS nvarchar(7)
AS   
-- Returns the input as valid time string 
BEGIN  
    DECLARE @ret nvarchar(7);              
        SET @ret = SUBSTRING(@myInput,1,2)+':'+ SUBSTRING(@myInput,3,2)+':00'
    RETURN @ret;  
END;

And call like:

SELECT ToTime(2030);
SELECT ToTime(1200);
...

Upvotes: 1

Related Questions