Convert Unix Date Time to Julian - DST

• lbrigham

  SSCommitted

  Points: 1567

  More actions

  March 2, 2010 at 3:21 pm

  #386417

  Copied from another thread I hijacked:

  CREATE FUNCTION [dbo].[ConvertUnixDate]

  (

  @TimeValue int

  )

  RETURNS datetime

  AS

  BEGIN

  DECLARE @DateTime datetime

  DECLARE @GMTOffsetInSeconds int

  DECLARE @isDateDST bit

  DECLARE @isCurrDateDST bit

  SET @GMTOffsetInSeconds = DateDiff(ss, GetUTCDate(), GetDate())

  IF DATEPART(m,GETDATE()) BETWEEN 4 AND 10

  SET @isCurrDateDST = 1

  ELSE

  SET @isCurrDateDST = 0

  IF DATEPART(m,DATEADD(s,@TimeValue+@GMTOffsetInSeconds,'01/01/1970')) BETWEEN 4 AND 10

  SET @isDateDST = 1

  ELSE

  SET @isDateDST = 0

  IF @isCurrDateDST = 1 AND @isDateDST = 0

  SET @GMTOffsetInSeconds = @GMTOffsetInSeconds - 3600

  IF @isCurrDateDST = 0 AND @isDateDST = 1

  SET @GMTOffsetInSeconds = @GMTOffsetInSeconds + 3600

  SELECT @DateTime = DATEADD(s,@TimeValue + @GMTOffsetInSeconds,'1/1/1970')

  RETURN @DateTime

  END

  When I was first approached with the challenge of converting Unix date time to a real date, I thought no problem after throwing a dateadd() and using datediff with GetUTCDate() for GMT offset...

  But I soon discovered the real challenge would be accommodating DST datetimes. By that I mean, I need to accommodate a non-DST datetime being evaluated during DST and vice versa.

  I've taken a break for the time being from trying to figure out the DST cutoff with starting at 2am 2nd Sun in Mar and ending at 2am 1st Sun in Nov, but above function will correctly convert to/from DST even if current date is DST and the date to convert is nonDST (currently if month is between Apr and Oct, then it's considered a DST datetime so doesn't account for the portion of Mar and Nov):

  SELECT dbo.ConvertUnixDate (DATEDIFF(ss,'01/01/1970','05/14/2010 2:30')+14400)

  +14400 to simulate the Unix datetime that would've been saved into the table during DST (effectively becomes same as GMT -4)

  Any help on getting the code added for cutoff in Mar and Nov would be greatly appreciated.

• Jeff Moden

  SSC Guru

  Points: 1004414

  More actions

  March 3, 2010 at 9:21 pm

  #1128228

  {edit} Never mind... misread the post...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)