convert datetime in utc seconds

• fasttrack

  SSCertifiable

  Points: 6071

  More actions

  November 17, 2005 at 3:15 pm

  #169615

  Hi guys,

  Do you know how to convert a datetime to UTC seconds:

  eg: nov 17, 2005 21:00:00

  UTC in seconds: 1132261200

  

   

   

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  November 17, 2005 at 7:42 pm

  #604556

  UTC Seconds, also called Epoch Start Seconds, is the number of seconds since January 1st, 1970, with the local date and time adjusted to the UTC timezone.

  declare @EpochStartTs datetime

  , @GregorianTs datetime

  -- date and time format is ODBC

  set @EpochStartTs = { ts '1970-01-01 00:00:00'}

  -- nov 17, 2005 21:00:00 in the Central Time Zone (Chicago, Illinois)

  set @GregorianTs = { ts '2005-11-17 21:00:00'}

  select datediff(ss,@EpochStartTs,@GregorianTs) as UTCSeconds_London

  , datediff(ss,@EpochStartTs,@GregorianTs)

  -- Add the timezone difference

  + datediff(ss,GETUTCDATE() , current_timestamp) as UTCSeconds_Chicago

  SQL = Scarcely Qualifies as a Language

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 17, 2005 at 8:13 pm

  #604557

  select convert(bigint, datediff(dd, '1970-01-01', GETUTCDATE()))*24*3600 + datediff(ss, dateadd(dd, datediff(dd, 0, GETUTCDATE()), 0), GETUTCDATE())

  _____________
  Code for TallyGenerator