SQL - convert long integer to datetime

I have a data开发者_开发问答base with dates in the following long integer format:

20100101000000

Where that would be Jan 1st, 2010, 00:00:00 for the time of day.

I want to be able to convert this to normal SQL Datetime syntax and back. Is this possible? I can only get this far:

SELECT CAST(CURRENT_TIMESTAMP as int);

Which returns '40556' - not exactly what Im after.

You could use substring to convert your string to yyyy-MM-dd HH:mm:ss notation, and cast that to a 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

You can stuff it with the spaces and colons required:

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;

The result is

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

The first one shows the string it is converted to, the 2nd the datetime value. To go in reverse

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

Replace the constants '20100101000000' and "getdate()" with field names where required if selecting from a table.