CODEWITHSUNDEEP
sql-server
Vasanthan
Vasanthan

Reputation: 310

Convert Datetime format in SQL Server 2008

I need to convert the Long value to a date time format.

Eg., Long value - 20080506015600658 datetime format - Tue May 06 01:56:00 IST 2008

Upvotes: 0

Views: 360

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

Here's the ugly way to do it via string manipulation:

declare @start bigint
set @start = 20080506015600658

select CONVERT(datetime,
    STUFF(STUFF(STUFF(STUFF(STUFF(STUFF(
        t,15,0,'.'),
          13,0,':'),
          11,0,':'),
           9,0,'T'),
           7,0,'-'),
           5,0,'-'))
from (select CONVERT(varchar(20),@start) as t) n

Which basically forces it to conform to the pattern YYYY-MM-DD'T'hh:mm:ss.mil before doing the conversion.

And here's the ugly as sin way to do it with maths:

declare @start bigint
set @start = 20080506015600658

select
    DATEADD(year,  (@start/10000000000000) - 1, --Because we already have 1 on starting date
    DATEADD(month, (@start/100000000000)%100 - 1, --Because we already have 1 on starting date
    DATEADD(day,   (@start/1000000000)%100 - 1, --Because we already have 1 on starting date
    DATEADD(hour,  (@start/10000000)%100,
    DATEADD(minute,(@start/100000)%100,
    DATEADD(second,(@start/1000)%100,
    DATEADD(millisecond,@start%1000,CONVERT(datetime2,'0001-01-01'))))))))

Upvotes: 1

Related Questions