Set Epoch timestamp in ms-sql

Question

I am trying to set the value of a particular field in MS-SQL 12 to the epoch timestamp at that moment. For now, I used a convoluted query:

UPDATE SOME_TABLE
    SET SOME_EPOCH_TIMESTAMP = CAST(DATEDIFF(second, '19700101', CURRENT_TIMESTAMP) AS BIGINT) * 1000
    WHERE SOME_CONDITION > 0;

What is a better way of doing this via MS-SQL builtins?

user_5176071 · Answer

I think DATEDIFF(s, '1970-01-01', GETUTCDATE()) is the best way.

If you want to let code clearer you can write a function.

CREATE FUNCTION [dbo].Epoch_TIMESTAMP ()
RETURNS BIGINT 
AS BEGIN
    RETURN DATEDIFF(s, '1970-01-01', GETUTCDATE())
END

using would be

SELECT [dbo].Epoch_TIMESTAMP()