Convert an integer to a date
-
In my SQL db I have a date store as an int. I need to be able to convert this
to a "read-able" date/time format (such as 1/1/2005 12:34:56 PM) to use in SQL
Reporting Services. How can I accomplish this?
As a point of reference:
If I take the integer and copy it into Excel (cell A1) I can then use the formula =A1/86400+DATE(1970,1,1) and then format the cell for date. The value ot the integer that is copied is 1084221013 and when converted it is 38117.85432 and when formated it becomes 5/10/04 8:30 PM. Or in other words, 1,084,221,013 seconds after 1/1/1970.
I can do the basic math in Reporting Services but the default formatting does not work and I have tried several different ways through custom code but nothing thus far works.
-
Create this function in your db and call it in your select statement (ie select dbo.convertunixtime(DATE)), this will convert the unixtime (int) to the real time.
CREATE FUNCTION dbo.convertunixtime (@UNIXTime int)
RETURNS datetime AS
BEGIN
declare @DSTStart datetime, @DSTEnd datetime
declare @GMTTime datetime, @Offset datetime
set @GMTTime = dateadd(ss, @UNIXTime, '1/1/1970 00:00')
set @DSTStart = '4/1/' + convert(varchar(5), Year(@GMTTime))
set @DSTEnd = '10/31/' + convert(varchar(5), Year(@GMTTime))
while datepart(dw, @DSTStart) <> 1
set @DSTStart = dateadd(dd, 1, @DSTStart)
while datepart(dw, @DSTEnd) <> 1
set @DSTEnd = dateadd(dd, -1, @DSTEnd)
if @GMTTime >= @DSTStart and @GMTTime <= @DSTEnd
set @Offset = '12/31/69 20:00'
else
set @Offset = '12/31/69 19:00'
return dateadd(ss, @UNIXTime, @Offset)
END
-
I borrowed this code for our Cisco CallManager database exports - works wonderfully for our purposes.
Thanks for posting!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply