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
Categories: MS SQL Server, Transact-SQL, TSQL
DBMS, MS SQL, MS SQL Server, sql2005, Transact-SQL, TSQL
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)
Good suggestion. Thanks.
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′});
?