convert unix timestamp to UTC

Hi

Is there a way in T-SQL (Server 2005) to convert unix timestamp to UTC

So want to do this:

1189481763.61 -> Tue, 11 Sep 2007 03:36:03 UTC(or any UTC format)

Thanks

You can use the DATEADD function to add the unix date time value to Jan 1 1970 12:00:00 AM.

Code Snippet

DECLARE @.unixDate FLOAT,

@.tsqlDate DATETIME

SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'

SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)

PRINT @.tsqlDate

|||

Hi

i was planning on doing that but but the problem with that is :

number

Is the value used to increment datepart. If you specify a value that is not an integer, the fractional part of the value is discarded. For example, if you specify day for datepart and1.75 for number, date is incremented by 1.

So it will truncate 1189481763.61 to 1189481763

I was looking for a work around

Thanks

|||

Code Snippet

DECLARE @.unixDate float,
@.tsqlDate DATETIME
SET @.unixDate = 1189481763.61

SET @.tsqlDate = '01/01/1970 00:00:00 AM'
SET @.tsqlDate = DATEADD(ss, @.unixDate, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)
-- Now convert the fractional part into millseconds and add to the date
SET @.tsqlDate = DATEADD(ms, (@.UnixDate - cast(@.unixDate as int)) *1000, @.tsqlDate)
PRINT convert(varchar(15),@.tsqlDate,114)