Unix Timestamp in SQL Server: Converting Seconds and Milliseconds Both Ways

A Unix Timestamp is the number of seconds that have passed since midnight UTC on 1 January 1970. APIs, logs and JavaScript use it everywhere, and SQL Server has no single function for it. Two short expressions convert in both directions, and a few traps wait around them.

What the Number Means

The starting moment, 1 January 1970 at 00:00:00 UTC, is called the epoch. A value with ten digits counts seconds from the epoch. A value with thirteen digits counts milliseconds, which is what JavaScript and many web APIs send.

Both forms are always UTC. They carry no time zone and no daylight saving rule. I ran every query here on SQL Server 2025. One value is our guide: 1392349571299 milliseconds should land on 14 February 2014, a little after 03:46 UTC.

Build the Test

The sample table is a small log of shop events. The API sent each time as milliseconds in a bigint column. The script can run twice.

IF DB_ID(N'SqlUnixTimeDemo') IS NULL CREATE DATABASE SqlUnixTimeDemo;
GO
USE SqlUnixTimeDemo;
GO
DROP TABLE IF EXISTS dbo.ApiEvents;
CREATE TABLE dbo.ApiEvents
(
    EventID int IDENTITY(1,1) PRIMARY KEY,
    EventName nvarchar(60) NOT NULL,
    UnixMs bigint NOT NULL
);
INSERT INTO dbo.ApiEvents (EventName, UnixMs) VALUES
(N'Order placed', 1392349571299),
(N'Payment received', 1392349589004),
(N'Parcel shipped', 1392435971000),
(N'Order delivered', 1392694800500);

Seconds to datetime2

DATEADD adds a number of seconds to the epoch. Use datetime2 as the base type. It is the modern date and time type, with more precision than the old datetime.

SELECT DATEADD(SECOND, 1392349571, CAST('1970-01-01' AS datetime2(0))) AS utc_time;

Seconds have no fraction, so datetime2(0) shows exactly what you have. Keep the CAST on the base date. Without it, a bare string like ‘19700101’ becomes the old datetime type, and the result inherits its limits.

Milliseconds and the Right Base Type

For milliseconds, change the datepart. The base type now matters. The old datetime stores time in steps of about three milliseconds, so it can’t hold .299. A datetime2(3) base keeps all three digits.

SELECT DATEADD(MILLISECOND, 1392349571299, CAST('1970-01-01' AS datetime)) AS datetime_base,
       DATEADD(MILLISECOND, 1392349571299, CAST('1970-01-01' AS datetime2(3))) AS datetime2_base;

The datetime result is off by one millisecond. That is small, but it breaks any equality check against the original number. The datetime2 result confirms our guide value: 03:46:11.299 UTC.

Back to a Unix Timestamp

The reverse trip uses DATEDIFF_BIG. It counts the datepart boundaries between the epoch and your value, and it returns a bigint. Seconds and milliseconds differ only in the datepart.

SELECT DATEDIFF_BIG(SECOND, '1970-01-01', CAST('2014-02-14 03:46:11' AS datetime2(0))) AS unix_seconds,
       DATEDIFF_BIG(MILLISECOND, '1970-01-01', CAST('2014-02-14 03:46:11.299' AS datetime2(3))) AS unix_ms;

The seconds value dropped the fraction of the millisecond time. DATEDIFF_BIG on the datetime2(3) value 03:46:11.299 also returns 1392349571, not 1392349572. It truncates, it doesn’t round.

For the current Unix time, use SYSUTCDATETIME. Don’t use GETDATE, because it returns the server’s local time. The numbers change on every call, so no result is printed here. In my run they had ten and thirteen digits.

SELECT DATEDIFF_BIG(SECOND, '1970-01-01', SYSUTCDATETIME()) AS now_seconds,
       DATEDIFF_BIG(MILLISECOND, '1970-01-01', SYSUTCDATETIME()) AS now_ms;

The Year 2038 Limit

An int holds numbers up to 2,147,483,647. Counted as seconds from the epoch, that is 19 January 2038 at 03:14:07 UTC. One second later, a Unix Timestamp stored in an int no longer fits.

On SQL Server 2025, DATEADD took the larger number without a complaint. It also worked with the test database at compatibility level 160. Older versions are documented to take only an int, and I didn’t test one.

SELECT DATEADD(SECOND, 2147483647, CAST('1970-01-01' AS datetime2(0))) AS last_int_second,
       DATEADD(SECOND, 2147483648, CAST('1970-01-01' AS datetime2(0))) AS one_more;

The limit survives on the way back. DATEDIFF returns an int, so it fails at the same moment. DATEDIFF_BIG returns a bigint and has no such problem.

SELECT DATEDIFF(SECOND, '1970-01-01', CAST('2038-01-19 03:14:08' AS datetime2(0))) AS int_diff;
GO
SELECT DATEDIFF_BIG(SECOND, '1970-01-01', CAST('2038-01-19 03:14:08' AS datetime2(0))) AS big_diff;

Msg 535, Level 16, State 1, Line 1
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

If a server can’t take a bigint in DATEADD, split the number. Add the whole days first, then the milliseconds left over. Both parts fit in an int, and the answer is the same as before.

DECLARE @ms bigint = 1392349571299;
SELECT DATEADD(MILLISECOND, @ms % 86400000, DATEADD(DAY, @ms / 86400000, CAST('1970-01-01' AS datetime2(3)))) AS split_result;

Time Zones

Every result so far is UTC. To show local time, mark the value as UTC and then convert it. The name Eastern Standard Time covers daylight saving too. The February value is five hours behind UTC, and the July value is four.

SELECT DATEADD(SECOND, 1392349571, CAST('1970-01-01' AS datetime2(0))) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS winter_local,
       DATEADD(SECOND, 1405305971, CAST('1970-01-01' AS datetime2(0))) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS summer_local;

The other direction needs the same care. A local time without a zone is ambiguous. Tell SQL Server the zone first, and DATEDIFF_BIG then counts from the matching UTC moment.

SELECT DATEDIFF_BIG(SECOND, '1970-01-01', CAST('2014-02-13 22:46:11' AS datetime2(0)) AT TIME ZONE 'Eastern Standard Time') AS from_local,
       DATEDIFF_BIG(SECOND, '1970-01-01', CAST('2014-02-13 22:46:11' AS datetime2(0))) AS forgot_zone;

The forgotten zone cost exactly 18,000 seconds, which is five hours. No error appears. The number looks fine and is wrong, and that makes this bug expensive.

Dates Before 1970

Negative numbers count backward from the epoch. The same two expressions handle them without changes.

SELECT DATEADD(SECOND, -1, CAST('1970-01-01' AS datetime2(0))) AS one_second_before,
       DATEADD(SECOND, -86400, CAST('1970-01-01' AS datetime2(0))) AS one_day_before,
       DATEDIFF_BIG(SECOND, '1970-01-01', CAST('1900-01-01' AS datetime2(0))) AS year_1900;

Mixing Up the Units

The most common mistake is the wrong datepart. A thirteen-digit millisecond value passed to SECOND points past the year 9999, so datetime2 refuses it. The opposite mistake is quiet, and that is the dangerous one.

SELECT DATEADD(SECOND, 1392349571299, CAST('1970-01-01' AS datetime2(0))) AS ms_as_seconds;
GO
SELECT DATEADD(MILLISECOND, 1392349571, CAST('1970-01-01' AS datetime2(3))) AS seconds_as_ms;

Msg 517, Level 16, State 3, Line 1
Adding a value to a 'datetime2' column caused an overflow.

A ten-digit number read as milliseconds lands on 17 January 1970. No error appears, and the date looks like valid data in a report. Count the digits before you choose the datepart: ten means seconds, thirteen means milliseconds.

A Reusable Pattern

For a whole column, keep the conversion in one place. The CROSS APPLY with VALUES below computes the UTC time once per row and gives it a name. Later columns reuse that name.

SELECT e.EventName, e.UnixMs, u.UtcTime,
       DATEDIFF_BIG(MILLISECOND, '1970-01-01', u.UtcTime) AS round_trip,
       u.UtcTime AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time' AS EasternTime
FROM dbo.ApiEvents AS e
CROSS APPLY (VALUES (DATEADD(MILLISECOND, e.UnixMs, CAST('1970-01-01' AS datetime2(3))))) AS u(UtcTime);

The round_trip column equals UnixMs in all four rows. That is a cheap proof that nothing was lost. Keep it in any test of your own conversion.

You could say the numbers should stay as bigint, since the API sends them that way. Fair point. A bigint is compact, sorts correctly and carries no zone confusion. But nobody reads 1392349571299 at a glance. A reporting query is easier to trust with a datetime2 column in UTC.

A Simple Rule

Store the moment as datetime2 in UTC. Keep the original Unix Timestamp only if you must send it back unchanged. Use datetime2(3) for milliseconds and DATEDIFF_BIG for the return trip. Convert to a local zone only when you show the value to a person.

Before you trust any conversion, test it with a value you know. Our guide value, 1392349571299, is a good one. If the result differs, check the datepart first. Then check the base type, and last the zone.

When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE SqlUnixTimeDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE SqlUnixTimeDemo;

A Unix Timestamp is not a date, it is a count of time since a date.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.