Current epoch — epochtime.tools · A precision instrument for time

Get the current Unix timestamp

SELECT DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME());        -- seconds
SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', SYSUTCDATETIME());   -- milliseconds

Convert a Unix timestamp to a date

SELECT DATEADD(SECOND, 1700000000, CAST('1970-01-01' AS datetime2));
-- 2023-11-14 22:13:20.0000000

-- Older versions take DATEADD's number as an int, which milliseconds overflow, so split them:
DECLARE @ms bigint = 1700000000123;
SELECT DATEADD(MILLISECOND, @ms % 1000,
       DATEADD(SECOND, @ms / 1000, CAST('1970-01-01' AS datetime2)));

Convert a date to a Unix timestamp

SELECT DATEDIFF_BIG(SECOND, '1970-01-01', '2026-11-14T10:00:00');   -- 1794650400

Timezones

-- AT TIME ZONE uses Windows zone names
SELECT DATEADD(SECOND, 1700000000, CAST('1970-01-01' AS datetime2))
       AT TIME ZONE 'UTC' AT TIME ZONE 'India Standard Time';
-- 2023-11-15 03:43:20.0000000 +05:30

Common mistakes

  • Older versions of DATEADD take the number as an int. Seconds then overflow after 2038-01-19, and milliseconds overflow right away. Split large values as shown above, or check your version's documentation, since newer releases may accept a bigint.
  • GETDATE() is the server's local time. Use SYSUTCDATETIME().
  • DATEDIFF (not _BIG) returns an int and overflows for milliseconds.
  • Zone names come from sys.time_zone_info, and they are Windows names such as Pacific Standard Time, not IANA names.

Complete example

DECLARE @epoch bigint = 1700000000;
SELECT
  DATEADD(SECOND, @epoch, CAST('1970-01-01' AS datetime2))                    AS utc,
  DATEDIFF_BIG(SECOND, '1970-01-01',
               DATEADD(SECOND, @epoch, CAST('1970-01-01' AS datetime2)))      AS back;

Check any value with the epoch converter, or convert a whole column with the batch converter. All examples use 1700000000 (2023-11-14 22:13:20 UTC) and 1794650400 (2026-11-14 10:00:00 UTC).

Official documentation

Last updated: