How do I convert a SQL server timestamp to an epoch timestamp?

Question

I know how to convert an epoch timestamp to a SQL server timestamp using DATEADD, but I am interested in a function that does the reverse of that.

Answer 1

You can do it using DATEDIFF function like below :

select DATEDIFF(s, '1970-01-01 00:00:00', '2017-11-20 23:12:02.000') as EpochTimeStamp

Output :

EpochTimeStamp
--------------
1511219522

You know already how can we get back the original date :

SELECT DATEADD(ss, 1511219522, '19700101') as OriginalDate

OriginalDate
-----------------------
2017-11-20 23:12:02.000