Technical Article

Calculate relative time

,

Sometimes, you just want to know what happened after a specific time. For example, you want to know what happened to the jobs last night. Or you want to know which backups were made since 10PM. This script does it all for you. It returns a datetime value based on 3 parameters. Only the first parameter is mandatory.

  • @StartHour integer representing the hour. pretty self-explaning but there's some coding added. If @StartHour is greater then the current hour, it's assumed as a time on yesterday
  • @DaysBack default: 0. Here you can add extra days in the past (negative value) or in the futue (positive value)
  • @TS timestamp to use. Default:current_timestamp. This allows you to change the base datetime

Example:

Let's say it's Sept 17 2015 11:14 AM

If I run

select dbo.udf_since(22,DEFAULT, DEFAULT)

This function will return Sept 16 2015 22:00  (assuming ysterday)

If I run

select dbo.udf_since(3,DEFAULT, DEFAULT)

This function will return Sept 17 2015 3:00  (assuming today)

If I run

select dbo.udf_since(3, -1, DEFAULT)

This function will return Sept 16 2015 3:00 (assuming today minus 1 day)

Function udf_since2 is an enhanced version for SQL 2008+ The first parameter for this version is a TIME datatype (which allowes you to use an exact time). If you use this version, enter:

select dbo.udf_since2('18:45', DEFAULT, DEFAULT)

if object_id('dbo.udf_since', 'FN') is not null
   drop function dbo.udf_since
GO

create function udf_since(@StartHour tinyint, @DaysBack int = 0, @TS datetime)
returns datetime as
   begin
      declare @tijdstip datetime

      set @ts = coalesce(@ts, CURRENT_TIMESTAMP)

      if @StartHour > datepart(hour, @ts)
         select @tijdstip = dateadd(day, @DaysBack -1 , dateadd(hour, @StartHour , dateadd(day, 0, datediff(day, 0, @ts))))
      else
         select @tijdstip = dateadd(day, @DaysBack , dateadd(hour, @StartHour , dateadd(day, 0, datediff(day, 0, @ts))))

      return (@tijdstip)
   end
go


if object_id('dbo.udf_since2', 'FN') is not null
   drop function dbo.udf_since2
GO

create function udf_since2(@StartHour time, @DaysBack int = 0, @TS datetime)
returns datetime as
   begin
      declare @tijdstip datetime

      set @ts = coalesce(@ts, CURRENT_TIMESTAMP)

      if @StartHour > cast(@ts as time)
         select @tijdstip = dateadd(day, @DaysBack -1 , dateadd(minute, datepart(minute, @StartHour), dateadd(hour, datepart(hour, @StartHour) , dateadd(day, 0, datediff(day, 0, @ts)))))
      else
         select @tijdstip = dateadd(day, @DaysBack, dateadd(minute, datepart(minute, @StartHour), dateadd(hour, datepart(hour, @StartHour) , dateadd(day, 0, datediff(day, 0, @ts)))))

      return (@tijdstip)
   end
go

Rate

2.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

2.8 (5)

You rated this post out of 5. Change rating