Convert Sql Server DateTime to Milliseconds Since 1970

Question

I want to convert sql server datetime to milliseconds . I tried to convert it with datediff function as below :

select cast(Datediff(ms, '1970-01-01',GETUTCDATE()) AS bigint)

But it's giving me this error:

Msg 535, Level 16, State 0, Line 2 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

I don't wanna do it like this :

select cast(Datediff(minute, '1970-01-01',GETUTCDATE()) AS bigint)*60*1000

Because it won't give me accurate results. Can somebody please help me on this?

Answer 1

For Sql Server before 2016 (e.g., 2008, 2012 et al), the code below may be a more acceptable approximation than just multiplying the result by 1000.

CREATE FUNCTION [dbo].[datetimeToUTCMilliseconds] (@ctimestamp as datetime) 
RETURNS bigint
AS
BEGIN
  /* Function body */
  declare @return bigint
   
  SELECT @return = (cast(DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp) as bigint) * 1000) + DATEPART(MILLISECOND, @ctimestamp);
   
  return @return
END

So you can use the relevant part below to convert sql server datetime to milliseconds:

SELECT (cast(DATEDIFF(SECOND,{d '1970-01-01'}, @yourdatetime) as bigint) * 1000) + DATEPART(MILLISECOND, @yourdatetime);

Answer 2

For SQL Server 2016, I created 2 functions:

create or alter function dbo.ufn_unixTime2LocalTime
(
     @UnixTimeStamp bigint
    ,@seconds_only bit = 0
) returns datetime
as
begin;
    declare @LocalTimeOffset bigint = datediff_big(millisecond,getdate(),getutcdate());
    declare @AdjustedTimeStamp bigint = (@UnixTimeStamp * iif(@seconds_only = 1,1000,1)) - @LocalTimeOffset;
    return dateadd(millisecond, @AdjustedTimeStamp % 1000, dateadd(second,@AdjustedTimeStamp / 1000, '19700101'));
end;
go
create or alter function dbo.ufn_localTime2UnixTime
(
     @localTime datetime
    ,@seconds_only bit = 0
) returns bigint
as
begin;
    declare @LocalTimeOffset bigint = datediff_big(millisecond,getdate(),getutcdate());
    return datediff_big(millisecond,'19700101',dateadd(millisecond,@LocalTimeOffset,@localTime)) / iif(@seconds_only = 1,1000,1);
end;
go

Answer 3

Here some SQL-code that does the job.
It assumes the DB server's local-time is Central European (Summer/Winter) Time (CET/CEST).
(it depends on whether you save UTC or local-time into your datetime-columns)

Note - Daylight saving time:
Change from winter-time to summer time (at end of march)
At the last sunday-morning in March, the clocks are put forward from 02:00 to 03:00 o'clock.
"One loses an hour. "

Change from summter-time to winter-time (at end of October)
At the last sunday morning in October, the clocks are being put backward from 03:00 to 02:00 o'clock.
"One wins an hour."

PRINT 'Begin Executing "01_fu_dtLastSundayInMonth.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_dtLastSundayInMonth]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fu_dtLastSundayInMonth]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



/*
SET DATEFIRST 3; -- Monday

WITH CTE AS (

    SELECT 1 AS i, CAST('20190101' AS datetime) AS mydate  
    UNION ALL 

    SELECT i+1 AS i, DATEADD(month, 1, CTE.mydate) AS mydate 
    FROM CTE WHERE i < 100 
)

SELECT -666 AS i, dbo.fu_dtLastSundayInMonth('17530101') AS lastSundayInMonth, dbo.fu_dtLastSundayInMonth('17530101') AS Control 

UNION ALL 

SELECT -666 AS i, dbo.fu_dtLastSundayInMonth('99991231') AS lastSundayInMonth, dbo.fu_dtLastSundayInMonth('99991231') AS Control 

UNION ALL 

SELECT 
     mydate 
    ,dbo.fu_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,dbo.fu_dtLastSundayInMonth(mydate) AS lastSundayInMonth 
    ,CAST(NULL AS datetime) AS Control
    --,DATEADD(day,DATEDIFF(day,'19000107', DATEADD(MONTH, DATEDIFF(MONTH, 0, mydate, 30))/7*7,'19000107') AS Control 
FROM CTE 

*/


-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Return Datum von letztem Sonntag im Monat 
--                mit gleichem Jahr und Monat wie @in_DateTime
-- =====================================================================
ALTER FUNCTION [dbo].[fu_dtLastSundayInMonth](@in_DateTime datetime )
RETURNS DateTime
AS
BEGIN
    -- Abrunden des Eingabedatums auf 00:00:00 Uhr
    DECLARE @dtReturnValue AS DateTime  
    -- 26.12.9999   SO
    IF @in_DateTime >= CAST('99991201' AS datetime) 
        RETURN CAST('99991226' AS datetime); 

    -- @dtReturnValue is now last day of month 
    SET @dtReturnValue = DATEADD 
        (
             DAY 
            ,-1
            ,DATEADD
            (
                 MONTH
                ,1
                ,CAST(CAST(YEAR(@in_DateTime) AS varchar(4)) + RIGHT('00' + CAST(MONTH(@in_DateTime) AS varchar(2)), 2) + '01' AS datetime) 
            )
        )
    ;

    -- SET DATEFIRST 1 -- Monday - Super easy ! 
    -- SET DATEFIRST != 1 - PHUK THIS ! 
    SET @dtReturnValue = DATEADD
                        (
                            day
                            ,
                             -
                             (

                                (
                                    -- DATEPART(WEEKDAY, @lastDayofMonth) -- with SET DATEFIRST 1 
                                    DATEPART(WEEKDAY, @dtReturnValue) + @@DATEFIRST - 2 % 7 + 1 
                                )
                                %7
                            )
                            , @dtReturnValue
    );

    RETURN @dtReturnValue; 
END


GO



GO 


PRINT 'Done Executing "01_fu_dtLastSundayInMonth.sql"' 


GO 





PRINT 'Begin Executing "02_fu_dtIsCEST.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_dtIsCEST]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fu_dtIsCEST]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fu_dtIsCEST('2019-03-31T01:00:00'), dbo.fu_dtIsCEST('2019-03-31T04:00:00') 
ALTER FUNCTION [dbo].[fu_dtIsCEST](@in_DateTime datetime )
RETURNS bit 
AS
BEGIN
    DECLARE @dtReturnValue AS bit   

    -- https://www.linker.ch/eigenlink/sommerzeit_winterzeit.htm

    -- Umstellung von Winterzeit auf Sommerzeit (Ende März):  
    -- Am letzten Sonntagmorgen im März werden die Uhren von 02:00 auf 03:00 Uhr vorgestellt. 
    -- Man verliert eine Stunde. 

    -- Umstellung von Sommerzeit auf Winterzeit (Ende Oktober): 
    -- Am letzten Sonntagmorgen im Oktober werden die Uhren von 03:00 auf 02:00 Uhr zurückgestellt. 
    -- Man gewinnt eine Stunde.

    DECLARE @beginSummerTime datetime 
    SET @beginSummerTime = dbo.fu_dtLastSundayInMonth(DATEADD(MONTH, 2, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )  
    SET @beginSummerTime = DATEADD(HOUR, 2, @beginSummerTime) 

    DECLARE @beginWinterTime datetime 
    SET @beginWinterTime = dbo.fu_dtLastSundayInMonth(DATEADD(MONTH, 9, DATEADD(YEAR, YEAR(@in_DateTime)-1900, 0)) )   
    SET @beginWinterTime = DATEADD(HOUR, 2, @beginWinterTime) 

    SET @dtReturnValue = 0; 
    IF @in_DateTime >= @beginSummerTime AND @in_DateTime < @beginWinterTime 
    BEGIN 
        SET @dtReturnValue = 1;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "02_fu_dtIsCEST.sql"' 


GO 





PRINT 'Begin Executing "03_fu_dtToEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_dtToEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fu_dtToEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 




-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fu_dtToEcmaTimeStamp('2019-03-31T01:00:00', 1), dbo.fu_dtToEcmaTimeStamp('2019-03-31T04:00:00', 1) 
ALTER FUNCTION [dbo].[fu_dtToEcmaTimeStamp](@in_DateTime datetime, @in_convert_to_utc bit)
RETURNS bigint 
AS
BEGIN
    DECLARE @dtReturnValue AS bigint    

    IF @in_convert_to_utc = 1 
    BEGIN
        SET @in_DateTime = 
        CASE WHEN dbo.fu_dtIsCEST(@in_DateTime) = 1 
            THEN DATEADD(HOUR, -2, @in_DateTime) 
            ELSE DATEADD(HOUR, -1, @in_DateTime) 
        END;
    END 

    SET @dtReturnValue = 
        CAST
        (
            DATEDIFF
            (
                HOUR
                ,CAST('19700101' AS datetime)
                ,@in_DateTime 
            )
            AS bigint
        ) *60*60*1000
        + 
        DATEDIFF
        (
             MILLISECOND 
            ,CAST(FLOOR(CAST(@in_DateTime AS float)) AS datetime) 
            ,@in_DateTime 
        ) % (60*60*1000)
    ;

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "03_fu_dtToEcmaTimeStamp.sql"' 


GO 





PRINT 'Begin Executing "04_fu_dtFromEcmaTimeStamp.sql"' 

GO 



IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fu_dtFromEcmaTimeStamp]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
    EXECUTE(N'CREATE FUNCTION [dbo].[fu_dtFromEcmaTimeStamp]() RETURNS int BEGIN RETURN 0 END ')
END 
GO 



-- =====================================================================
-- Author:        Stefan Steiger
-- Create date:   01.03.2019
-- Last modified: 01.03.2019
-- Description:   Ist @in_DateTime Mitteleuropäische Sommerzeit ? 
-- =====================================================================
-- SELECT dbo.fu_dtFromEcmaTimeStamp('1551437088122', 1), dbo.fu_dtFromEcmaTimeStamp('1554069600000', 1) 
ALTER FUNCTION [dbo].[fu_dtFromEcmaTimeStamp](@in_timestamp bigint, @in_convert_to_localtime bit)
RETURNS datetime 
AS
BEGIN
    DECLARE @dtReturnValue AS datetime      
    DECLARE @hours int 
    SET @hours = @in_timestamp /(1000*60*60);

    DECLARE @milliseconds int 
    SET @milliseconds = @in_timestamp - (@in_timestamp /(1000*60*60))*(1000*60*60);


    SET @dtReturnValue = DATEADD 
                        (
                            MILLISECOND, @milliseconds, 
                            DATEADD(hour, @hours, CAST('19700101' AS datetime)) 
                        ) 


    IF @in_convert_to_localtime = 1 
    BEGIN
        SET @dtReturnValue = DATEADD(HOUR, 1, @dtReturnValue) 
        SET @dtReturnValue = 
                                CASE WHEN dbo.fu_dtIsCEST(@dtReturnValue) = 1 
                                    THEN DATEADD(HOUR, 1, @dtReturnValue) 
                                    ELSE @dtReturnValue 
                                END;
    END 

    RETURN @dtReturnValue;
END


GO



GO 


PRINT 'Done Executing "04_fu_dtFromEcmaTimeStamp.sql"' 


GO 

Answer 4

With this query can you get the DateTime to Milliseconds Since 1970

SELECT CAST(Datediff(s, '1970-01-01', GETUTCDATE()) AS BIGINT)*1000

Answer 5

select Datediff_big(MS, '1970-01-01', GETUTCDATE())

I confirmed the result.

Found at mitchfincher.blogspot.com/2013/09/convert-sql-server-datetime-to.html in an anonymous comment.

Answer 6

For the people still looking for this, you can use the DATEDIFF_BIG function. This is supported in SQL 2016+, Azure

https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-big-transact-sql

Answer 7

Here is a Microsoft SQL function that returns UTC time in milliseconds(Milliseconds since 1970) its result is equal to Java.currentTimeMillis()

CREATE FUNCTION dbo.currentTimeMilliseconds()
  RETURNS BIGINT
  WITH EXECUTE AS CALLER
AS
  BEGIN

    DECLARE @t datetime = CONVERT (datetime, GETUTCDATE());
    DECLARE @days BIGINT = Datediff(day, '1970-01-01',@t);
    DECLARE @t_hours BIGINT = DATEPART(HOUR, @t);
    DECLARE @t_minuts BIGINT = DATEPART(MINUTE, @t);
    DECLARE @t_seconds BIGINT = DATEPART(SECOND, @t);
    DECLARE @t_miliseconds BIGINT = DATEPART(MILLISECOND, @t);

    RETURN @days * 1000 * 60 * 60 * 24 + @t_hours * 60 *60 *1000 + @t_minuts * 60 * 1000 + @t_seconds * 1000 + @t_miliseconds;
  END
GO

Answer 8

Are you sure you need it down to milliseconds (thousandths of a second)?

Be aware that 1 day = 86,400,000ms (yes, 86.4 million)

1 year = approx 31.6 billion milliseconds.

1970 was (as of today) 44 years ago, so that is approx 1.4 trillion milliseconds ago.

Sure, a bigint can handle it, unfortunately you also hit a limit of DATEDIFF, here's a quote from the documentation:

If the return value is out of range for int (-2,147,483,648 to +2,147,483,647), an error is returned. For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. For second, the maximum difference is 68 years.

So, you're safe getting the difference in seconds for a while (as long as you don't go too far into the future), and you could then count milliseconds from the start of today, e.g.:

SELECT 
  CAST(DATEDIFF(second, '1970-01-01', CAST(GetUtcDate() AS date)) AS bigint)
    AS [SecondsToStartOfDay], 
  DATEDIFF(ms, CAST(GetUtcDate() AS date), GetUtcDate())
    AS [MillisecondsSinceStartOfDay],
  (CAST(DATEDIFF(second, '1970-01-01', CAST(GetUtcDate() AS date)) AS bigint)*1000) 
  + DATEDIFF(ms, CAST(GetUtcDate() AS date), GetUtcDate()) 
    AS [Milliseconds]

The first two columns are just to show the steps involved.