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.

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

Use it like this:

SELECT UNIX_TIMESTAMP_TO_DATETIME(1232980434);
GO

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

About these ads
  1. March 27, 2010 at 2:01 am | #1

    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 | #2

      Good suggestion. Thanks.

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

    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.
Follow

Get every new post delivered to your Inbox.

%d bloggers like this: