SQL - convert long integer to datetime

Question

I have a database with dates in the following long integer format:

20100101000000

If it will be January 1, 2010, 00:00:00 at time of day.

I want to be able to convert this to regular SQL Datetime syntax and vice versa. Is it possible? I can only go this far:

SELECT CAST(CURRENT_TIMESTAMP as int);

What returns '40556' is not exactly what Im is after.

Answer 1

You can use substringto convert your string to a note yyyy-MM-dd HH:mm:ssand apply it to datetime:

select  convert(datetime, 
            substring(sub.Dt,1,4) + '-' + 
            substring(sub.Dt,5,2) + '-' + 
            substring(sub.Dt,7,2) + ' ' +
            substring(sub.Dt,9,2) + ':' + 
            substring(sub.Dt,11,2) + ':' + 
            substring(sub.Dt,13,2))
from    (
        select  '20100101000000' as Dt
        ) sub

Answer 2

You can fill it with spaces and colons:

select
stuff(stuff(stuff('20100101000000',9,0,' '),12,0,':'),15,0,':') STR,
convert(datetime,stuff(stuff(stuff('20100101000000',9,0,' '),12,0,':'),15,0,':')) DT;

Result

STR               | DT
20100101 00:00:00 | 2010-01-01 00:00:00.000

, , datetime.

select
convert(char(8),getdate(),112) + replace(convert(varchar(30),getdate(),108),':','');

"20100101000000" "getdate()" , .