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

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

2.8 (5)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

Calculating Duration Using DATETIME Start and End Dates (SQL Spackle)

It's an old problem with a solution that's nearly as old. SQL Server MVP Jeff Moden shows us the old trick mixed with a slick trick to format the duration as extended hours, minutes, seconds, and milliseconds.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.84 (50)

You rated this post out of 5. Change rating

2015-04-03 (first published: )

60,818 reads

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,588 reads