Convert Unix Timestamp in SQL Server — DATEADD and DATEDIFF in T-SQL

SQL Server is the only major database without a built-in FROM_UNIXTIME or to_timestamp function. Microsoft never added one, even in SQL Server 2022. So we have to use DATEADD with a reference date.

The standard pattern is DATEADD(SECOND, unix_ts, '1970-01-01'). It works, it is fast, and it has been the answer on Stack Overflow for fifteen years.

Convert a Unix Timestamp Using DATEADD

-- Unix seconds to datetime (UTC)
SELECT DATEADD(SECOND, 1711000000, '1970-01-01');
-- 2024-03-21 07:46:40.000

-- For milliseconds, use SECOND with division
SELECT DATEADD(SECOND, 1711000000123 / 1000, '1970-01-01');

-- For higher precision (datetime2 supports microseconds)
SELECT DATEADD(MILLISECOND, 1711000000123 % 1000,
       DATEADD(SECOND, 1711000000123 / 1000, CAST('1970-01-01' AS datetime2)));

The result is in UTC because '1970-01-01' is interpreted as a literal datetime with no zone. To convert to local time, use AT TIME ZONE (SQL Server 2016+):

SELECT DATEADD(SECOND, 1711000000, '1970-01-01')
       AT TIME ZONE 'UTC'
       AT TIME ZONE 'Eastern Standard Time';

Note that SQL Server uses Windows timezone names ("Eastern Standard Time", "Pacific Standard Time"), not IANA names ("America/New_York"). This is annoying if you are coming from Postgres or any non-Windows system.

Convert a Datetime to Unix Timestamp with DATEDIFF

-- Current Unix timestamp (seconds)
SELECT DATEDIFF(SECOND, '1970-01-01', GETUTCDATE());
-- e.g. 1744070000

-- From a column
SELECT DATEDIFF(SECOND, '1970-01-01', created_at) FROM orders;

-- For dates after year 2068, DATEDIFF SECOND overflows int
-- Use DATEDIFF_BIG for safety on future-proof code
SELECT DATEDIFF_BIG(SECOND, '1970-01-01', GETUTCDATE());

Two things to be careful about. First, use GETUTCDATE(), not GETDATE() — the latter returns local server time which makes your epoch values dependent on what time zone the server is in. Second, DATEDIFF with seconds returns an int, which overflows in the year 2068. DATEDIFF_BIG returns bigint and is the safer choice for new code.

Sell Custom Apparel — We Handle Printing & Free Shipping

CURRENT_TIMESTAMP, GETDATE, and SYSUTCDATETIME

SELECT CURRENT_TIMESTAMP;  -- ANSI standard, same as GETDATE()
SELECT GETDATE();          -- T-SQL classic, returns local server time
SELECT GETUTCDATE();       -- UTC version
SELECT SYSDATETIME();      -- Local with higher precision (datetime2)
SELECT SYSUTCDATETIME();   -- UTC with higher precision

-- Default in CREATE TABLE
CREATE TABLE events (
    id INT IDENTITY PRIMARY KEY,
    created_at datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
    updated_at datetime2 NOT NULL DEFAULT SYSUTCDATETIME()
);

For new SQL Server code, prefer SYSUTCDATETIME() over GETUTCDATE(). The newer function returns datetime2 with 100-nanosecond precision instead of the legacy datetime type with 3.33-millisecond precision. Storage is the same, precision is better, range is wider (year 1 to 9999 vs 1753 to 9999).

SQL Server Timestamp Pitfalls

Pitfall 1: SQL Server's "TIMESTAMP" type is NOT a timestamp

The TIMESTAMP type in SQL Server is a row version counter — it has nothing to do with dates or epoch time. It is now deprecated in favor of rowversion. Never use it for actual timestamp data. Use datetime2 or datetimeoffset instead.

Pitfall 2: GETDATE returns local server time

If your SQL Server is on a machine in EST and your application is in PST, GETDATE returns EST. This causes timestamps to drift across deployments. Always use GETUTCDATE or SYSUTCDATETIME and store everything in UTC.

Pitfall 3: Windows timezone names

SQL Server uses Windows timezone names like "Eastern Standard Time" — which is actually US Eastern with DST, not the EST acronym. The names are documented in sys.time_zone_info. Coming from Postgres or any IANA-based system, this is the most surprising thing about SQL Server time handling.

Pitfall 4: DATEDIFF int overflow

DATEDIFF with SECOND overflows int (about 68 years). For future-proof code use DATEDIFF_BIG. The signature is identical, only the return type differs.

For verifying any specific value without writing T-SQL, the free Unix timestamp converter handles seconds and milliseconds.

Frequently Asked Questions

How do I convert a Unix timestamp to a date in SQL Server?

Use DATEADD(SECOND, unix_timestamp, "1970-01-01"). SQL Server has no built-in FROM_UNIXTIME function, so this is the standard pattern. The result is in UTC because the literal "1970-01-01" has no timezone.

How do I get a Unix timestamp from a date in SQL Server?

Use DATEDIFF(SECOND, "1970-01-01", GETUTCDATE()) for the current Unix timestamp. For future-proof code, use DATEDIFF_BIG which returns bigint instead of int (DATEDIFF int overflows in 2068).

Is SQL Server's TIMESTAMP type a Unix timestamp?

No — and this confuses developers constantly. SQL Server's TIMESTAMP type is actually a row version counter, not a timestamp at all. It is now deprecated in favor of rowversion. For actual date and time data, use datetime2 or datetimeoffset.

What is the difference between GETDATE and GETUTCDATE?

GETDATE returns the local server time. GETUTCDATE returns the same instant in UTC. For application code that needs consistent timestamps regardless of where the server lives, always use GETUTCDATE or its higher-precision sibling SYSUTCDATETIME.

Why does SQL Server use "Eastern Standard Time" instead of "America/New_York"?

SQL Server uses Windows timezone names instead of IANA names. The naming is unusual — "Eastern Standard Time" actually refers to the US Eastern zone with daylight savings, not the EST acronym. The full list is in the sys.time_zone_info system view.

Should I use datetime or datetime2 in SQL Server?

Use datetime2 for new code. It has 100-nanosecond precision instead of datetime's 3.33-millisecond precision, supports years 1 through 9999, and uses the same or less storage. The legacy datetime type only exists for backward compatibility.