Unix timestamps in SQL Server
SQL Server has no Unix functions, so you add to or subtract from 1970-01-01. The catch is that older versions of DATEADD take the number as an int.
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
DATEADDtake the number as anint. 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. UseSYSUTCDATETIME().DATEDIFF(not_BIG) returns anintand overflows for milliseconds.- Zone names come from
sys.time_zone_info, and they are Windows names such asPacific 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).