SQL Conversion Between Datetime and Unix Timestamp

SQL Conversion Between Datetime and Unix Timestamp

Purpose: To convert a datetime value in T-SQL to Unix Timestamp and vice versa.

Note:

  1. Unix datetime is the number of seconds since 1/1/1970 in Greenwich Mean Time (GMT).
  2. The following code converts a T-SQL Datetime (based on local server time) to a Unix Timestamp: DATEDIFF(SECOND,’19700101′,@StartDT) + DATEDIFF(second,GETDATE(),GETUTCDATE())
  3. The following code converts a Unix Timestamp to a T-SQL Datetime (based on local server time): DATEADD(SECOND,-DATEDIFF(second,GETDATE(),GETUTCDATE()),DATEADD(second,@UTCTimestamp,’19700101′));

Leave a Reply