Home > MS SQL Server, Transact-SQL, TSQL > T-SQL Function to convert Unix Timestamp into MS SQL datetime

T-SQL Function to convert Unix Timestamp into MS SQL datetime

January 26, 2009

Okay. Previously we converted datetime type values into unix timestamps. Now here is how to convert them back.

@datetime = DATEADD(second, @timestamp, {d '1970-01-01})

Where @timestamp is the unix timestamp we want to convert.
The {d ‘yyyy-mm-dd’} is an ODBC escape sequence.

How about making this into a function so we don’t have to write all these down each and every time we want to make a conversion.

@timestamp integer
RETURNS datetime
  /* Function body */
  DECLARE @return datetime
  SELECT @return = DATEADD(second, @timestamp,{d '1970-01-01'});
  RETURN @return

Use it like this:


Twit This Post!

Add to FacebookAdd to DiggAdd to Del.icio.usAdd to StumbleuponAdd to RedditAdd to BlinklistAdd to Ma.gnoliaAdd to TechnoratiAdd to FurlAdd to Newsvine

  1. March 27, 2010 at 2:01 am

    Well done, good post. People might get stuck in SQL Server if they get an error mentioning that the function is not defined. To fix that, prefex it with the schema name (e.g. dbo):

    dbo. UNIX_TIMESTAMP_TO_DATETIME(123456789)

    • March 27, 2010 at 12:03 pm

      Good suggestion. Thanks.

  2. Chris R.
    September 9, 2011 at 12:00 am

    Thanks for this post it helped me figure out this. But One question, why the Declare and select statement, can’t you just

    RETURN DATEADD(second, @timestamp,{d ‘1970-01-01’});

  1. No trackbacks yet.
Comments are closed.

Get every new post delivered to your Inbox.

%d bloggers like this: