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


 

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ac470e4-f155-43e4-b9a3-76227cf10ec3/sqlite-query-want-to-convert-in-ms-sql-server-2008?forum=transactsql

Advertisements


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s