Convert unix timestamp to datetime in MS SQL

[sdever]

I have a system that is sending logs to a MS SQL database. The problem is that the time is passed as unix timestamp. For example 1078576968. I need to convert it to a datetime column. How do I do this?

[MAK]

--unix timestamp = seconds since 1/1/1970
declare @unixtimestamp int
declare @oneday int
declare @remainingseconds decimal(20,4)
declare @date datetime
set @unixtimestamp =1078576968
set @oneday= 86400 --60 min *60 sec * 24 hrs
set @date=dateadd(day,@unixtimestamp /@oneday,'01/01/1970')
select @date as Roundeddate
set @remainingseconds =(convert(decimal(20,4),@unixtimestamp) /convert(decimal(20,4),@oneday)) -(@unixtimestamp /@oneday)
select dateadd(ss,@remainingseconds *86400,@date) as Precisiondate



--I didn't had time to optimize the code. Feel free to optimize it.

[MAK]

--results
Roundeddate
2004-03-06 00:00:00.000

(1 row(s) affected)

Precisiondate
2004-03-06 12:42:46.000

(1 row(s) affected)

[MAK]

--realized something

select dateadd(ss,1078576968,'01/01/1970')

2004-03-06 12:42:48.000


--Please ignore my previous posting reg this.

[sdever]

Thank You That is exactly what I was looking for. Stupid me I should have thought of that first.