Convert Sql Server DateTime to Milliseconds Since 1970

Question

I want to convert datetime sql server to milliseconds. I tried converting it with a dated function, as shown below:

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

But this gives me this error:

Msg 535, Level 16, State 0, Line 2 A dated function led to overflow. The number of dates separating two instances of date / time is too large. Try using a less accurate date datiff.

I do not want to do it like this:

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

Because it will not give me exact results. Can someone please help me with this?

Answer 1

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

Keep in mind that 1 day = 86,400,000 ms (yes, 86.4 million)

1 = 31,6 .

1970 ( ) 44 , 1,4 .

, bigint , , DATEDIFF, :

int (-2,147,483,648 +2,147,483,647), . startdate enddate 24 , 20 , 31 ​​23,647 . -, 68 .

, ( ), , :

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]

.

Answer 2

Microsoft SQL, UTC ( 1970 ), 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 3

, , DATEDIFF_BIG. SQL 2016+, Azure

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

Answer 4

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

.

mitchfincher.blogspot.com/2013/09/convert-sql-server-datetime-to.html .

Answer 5

DateTime 1970

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