SQL SERVER – Converting Unix TimeStamp to DateTime

There is a very easy method to convert Unix TimeStamp to DateTime. Let us learn that in today’s blog post.

Here is a very simple example of it.

SQL SERVER - Converting Unix TimeStamp to DateTime UnixTimeStamp-800x388

Unix TimeStamp

DECLARE @UnixDate BIGINT = 1392349571299
DATEADD(s, @UnixDate / 1000, '1970-01-01')) AS DATETIME2(3))

When you run the script above it will display the result as 2014-02-14 03:46:11.300

Yes, that’s it. It is that simple to convert Unix TimeStamp to Datetime.

Learn MySQL – Learning Path

Here are six-part blog post series I have written based on my last 10 years of experience helping with the Comprehensive Database Performance Health Check. I strongly recommend you to read them as they walk you through my business model.

If you have any questions, you can always reach out to me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL DateTime, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Source Database in Restoring State
Next Post
SQL SERVER – Fix Error – Cannot execute as the database principal because the principal “dbo” does not exist

Related Posts

2 Comments. Leave new

  • Simple, but wrong. The 299 miliseconds are converted to 300.

    This is the correct conversion

    SELECT DATEADD(SECOND, @UnixDate / 1000, DATETIME2FROMPARTS(1970, 1, 1, 0, 0, 0, @UnixDate % 1000, 3));

  • And to overcome the INT problem, use this

    DECLARE @UnixDate BIGINT = 4592349571299;

    SELECT DATEADD(MILLISECOND, @UnixDate % 86400000, DATEADD(DAY,@UnixDate / 86400000, CAST(‘19700101 00:00:00.000’ AS DATETIME2(3))));


Leave a Reply