0

I have a SQL function that was provided which convert epoch to a date time.

The issues that this sql function does not work for dates before 1/3/1970. Does anyone have any ideas to make this work for dates less than 1970.

DECLARE @total bigint
--if greater than 12/31/9999 return null
IF @total > 253402232400000
    RETURN NULL

--if less than or equal 1/3/1970 return null        
IF @total <= 18000000
  RETURN NULL

DECLARE @seconds int = @total / 86400000;
DECLARE @milliseconds int = @total % 86400000;

DECLARE @result datetime = '1970-1-1';

SET @result = DATEADD(DAY, @seconds,@result);
SET @result = DATEADD(MILLISECOND, @milliseconds,@result);

RETURN @result;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Apollo
  • 1,990
  • 12
  • 44
  • 65
  • Have you tried using a negative value? – Gordon Linoff Dec 13 '18 at 18:17
  • for what value? – Apollo Dec 13 '18 at 18:26
  • Read the second comment in your code. What does that tell you? Anything? – SMor Dec 13 '18 at 19:54
  • By definition I don't believe you can. Epoch time is a point in time as the number of seconds that have elapsed since 00:00:00 UTC 1970-1-1. – Tim Mylott Dec 13 '18 at 19:59
  • @SMor it looks you have not ran the code. – Apollo Dec 13 '18 at 20:00
  • 1
    Don't need to run the code. That comment is very clear. The code looks for a lower boundary and returns NULL. You were provided a function (well, at least an incomplete script) that intentionally returns NULL for dates prior to July 28 1970 (not Jan 3). Why? Go ask the provider. You can learn all about that [here](https://www.epochconverter.com/) – SMor Dec 13 '18 at 20:15

1 Answers1

1

Try this.
Should work for all dates from 0001-01-01T00:00:00.000 through 9999-12-31T23:59:59.999.

-- UnixTimeToDateTime2
--
-- Parameter:  64-bit integer
-- Number of milliseconds 
-- since 1970-01-01T00:00:00.000
-- May be negative before 1970
--
-- Returns datetime2
-- Works with all values in  
-- range   0001-01-01T00:00:00.000
-- through 9999-12-31T23:59:59.999

-- Returns NULL if parameter is out of range

create function dbo.UnixTimeToDateTime2(@x bigint)
returns datetime2 
as 
begin 
  return
    case 
      -- If the parameter is out of range,
      -- return NULL
      when    ( @x < -62135596800000 ) 
           or ( @x > 253402300799999 ) then null
      else

      -- We would like to add this number of milliseconds
      -- directly to 1970-01-01T00:00:00.000, but this 
      -- can lead to an overflow.
      -- Instead we break the addition into a number of days
      -- and a number of milliseconds. 
      -- To get the number of days, we divide by the number 
      -- of milliseconds in a day. Then add the remainder.

        dateadd ( millisecond, 
                  @x % 86400000, 
                  dateadd ( day, 
                            @x / 86400000, 
                            cast( '1970-01-01T00:00:00.000' 
                                  as datetime2 )) )
    end
end
David Dubois
  • 3,842
  • 3
  • 18
  • 36