none
SSMS 2014 :Gregorian date to regular date sql server RRS feed

  • Question

  • Hello Geeks,

    Could you please provide a way to convert Gregorian date to regular date time.

    Please find two examples as below.

    1573839767253--> Nov 15, 2019 12:42:47 PM
    1573839969454--> Nov 15, 2019 12:46:09 PM

    Thanks In Advance,

    PC


    pc

    Monday, November 18, 2019 8:42 PM

Answers

  • create table #temp (eventtimegmt bigint null, eventtimezone int null)
     --CST -18000 seconds
    Insert into #temp values(1573839767253/1000 , -18000)
    ,(1573839969454/1000,-18000)
       
      
      
      Select eventtimegmt as  epochdt 
      ,  DATEADD(second,eventtimegmt , CAST('1970-01-01 00:00:00' AS datetime)) as gmtdt 
      , DATEADD(second,(eventtimegmt + eventtimezone) , CAST('1970-01-01 00:00:00' AS datetime)) as localdt
     
      from #temp
     
     drop table #temp

    Monday, November 18, 2019 9:00 PM
    Moderator

All replies

  • create table #temp (eventtimegmt bigint null, eventtimezone int null)
     --CST -18000 seconds
    Insert into #temp values(1573839767253/1000 , -18000)
    ,(1573839969454/1000,-18000)
       
      
      
      Select eventtimegmt as  epochdt 
      ,  DATEADD(second,eventtimegmt , CAST('1970-01-01 00:00:00' AS datetime)) as gmtdt 
      , DATEADD(second,(eventtimegmt + eventtimezone) , CAST('1970-01-01 00:00:00' AS datetime)) as localdt
     
      from #temp
     
     drop table #temp

    Monday, November 18, 2019 9:00 PM
    Moderator
  • Jingyang's answer seems to be correct. I only want to add a note on terminology. The Gregorian calendar that is what we use every day - that is regular time. The numbers you have are timestamps from Unix - the number of seconds since Jan 1st 1970.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, November 18, 2019 10:57 PM
  • worked as a charm.. Many thanks

    Regards,

    PC


    pc

    Tuesday, November 19, 2019 4:42 PM