Handy reporting functions (sqlserver)

[cmatthews]

I made some web based drillable jasperReports against the event_log tables and found these functions to be handy for date logic. I didn't write them (found them) but since they are useful for netxms table reporting thought I'd share.

CREATE function [dbo].[ufn_DATETIME_TO_UNIX_TIME]
    ( @DAY datetime )
returns  int
as
/*
Function: ufn_DATETIME_TO_UNIX_TIME

    Finds UNIX time as the difference in seconds between
    1970-01-01 00:00:00 and input parameter @DAY after
    rounding @DAY to the neareast whoie second.

    Valid datetime range is 1901-12-13 20:45:51.500 through
    2038-01-19 03:14:07.497.  This range is limited to the smallest
    through the largest possible integer.

    Datetimes outside this range will return null.
*/
begin
declare @wkdt datetime

-- Return null if outside of valid UNIX Time range
if @DAY < '1901-12-13 20:45:51.500' or  @DAY > '2038-01-19 03:14:07.497'
    return null

-- Round off datetime to nearest whole second
select @wkdt = dateadd(ms,round(datepart(ms,@DAY),-3)-datepart(ms,@DAY),@DAY)

-- If date GE 1901-12-14
if @wkdt >= 712 return datediff(ss,25567,@wkdt)

-- Handles time GE '1901-12-13 20:45:52.000 and LT 1901-12-14
return -2147472000-datediff(ss,@wkdt,712)

end

CREATE function [dbo].[ufn_UNIX_TIME_TO_DATETIME]
    ( @UNIX_TIME int )
returns  datetime
as
/*
Function: ufn_UNIX_TIME_TO_DATETIME

    Converts UNIX time represented as the difference
    in seconds between 1970-01-01 00:00:00 to a datetime.

    Any valid integer -2,147,483,648 through 2,147,483,647
    can be converted to datetime.
*/
begin

return  dateadd(ss,@UNIX_TIME,'1969-12-31 16:00:00')

end