Author Topic: Handy reporting functions (sqlserver)  (Read 8608 times)

cmatthews

  • Newbie
  • *
  • Posts: 5
    • View Profile
Handy reporting functions (sqlserver)
« on: February 11, 2011, 09:00:19 pm »
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