A sample of Sqlite datetime (EPOCH) Converted to SQL Server

create table #temp (eventtimegmt bigint null, eventtimezone int null)
Insert into #temp values(1389595157 , 19800)
Insert into #temp values(1302341760 , 0)

  ;with mycte0 as (
  Select eventtimegmt, dt0=DATEADD(second,eventtimegmt, CAST('1970-01-01 00:00:00' AS datetime)) 
  ,dt=DATEADD(second,eventtimegmt + eventtimezone, CAST('1970-01-01 00:00:00' AS datetime))  from #temp)

,mycte as (SELECT eventtimegmt as epochdt, dt0 as gmtdt ,  dt as localdt
, DATEADD(minute, DATEDIFF(minute,0,dt),0) as StartOftheMinute
 ,DATEADD(hour, DATEDIFF(hour,0,dt),0) as StartOftheHour
, DATEADD(day, DATEDIFF(day,0,dt),0) as StartOftheday
, DATEADD(mm, DATEDIFF(mm,0,dt),0) as StartOfThisMonth
, DATEADD(year, DATEDIFF(year,0,dt),0) as StartOftheYear
, DATEADD(wk, DATEDIFF(wk,0,dt),0) as StartOftheWeek FROM mycte0)

select epochdt,
Left(datename(weekday,localdt),3) +' '+ CONVERT(varchar(26),localdt,109) as localdt
,Left(datename(weekday,gmtdt),3) +' '+ CONVERT(varchar(26),gmtdt,109) as gmtdt
,Left(datename(weekday,StartOftheMinute),3) +' '+ CONVERT(varchar(26),StartOftheMinute,109) as StartOftheMinute
,Left(datename(weekday,StartOftheHour),3) +' '+ CONVERT(varchar(26),StartOftheHour,109) as StartOftheHour

,Left(datename(weekday,StartOftheday),3) +' '+ CONVERT(varchar(26),StartOftheday,109) as StartOftheday
,Left(datename(weekday,StartOfThisMonth),3) +' '+ CONVERT(varchar(26),StartOfThisMonth,109) as StartOfThisMonth
,Left(datename(weekday,StartOftheYear),3) +' '+ CONVERT(varchar(26),StartOftheYear,109) as StartOftheYear
,Left(datename(weekday,StartOftheWeek),3) +' '+ CONVERT(varchar(26),StartOftheWeek,109) as StartOftheWeek

from mycte

drop table #temp




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s