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
