Date ranges in T/SQL

Question

For a current project I am working I need to return an aggregate report based on date ranges.

I have 3 types of reports, yearly, monthly and daily.

To assist in returning this report I need a function that will return all of the sub-ranges of datetimes, within a big range.

So for example if I as for all the daily ranges between '2006-01-01 11:10:00' and '2006-01-05 08:00:00' I would expect the following results.

select * 
from dbo.fnGetDateRanges('d', '2006-01-01 11:10:00', '2006-01-05 08:00:00')  

2006-01-01 11:10:00.000,    2006-01-02 00:00:00.000
2006-01-02 00:00:00.000,    2006-01-03 00:00:00.000
2006-01-03 00:00:00.000,    2006-01-04 00:00:00.000
2006-01-04 00:00:00.000,    2006-01-05 00:00:00.000
2006-01-05 00:00:00.000,    2006-01-05 08:00:00.000

For the yearly range of '2006-01-01 11:10:00' to '2009-05-05 08:00:00', I would expect.

select * 
from dbo.fnGetDateRanges('y', '2006-01-01 11:10:00', '2009-05-05 08:00:00') 

2006-01-01 11:10:00.000,    2007-01-01 00:00:00.000
2007-01-01 00:00:00.000,    2008-01-01 00:00:00.000
2008-01-01 00:00:00.000,    2009-01-01 00:00:00.000
2009-01-01 00:00:00.000,    2009-05-05 08:00:00.000

How would I implement this function?

Answer 1

There are quite a few tricks in here, hope you find it useful

create function dbo.fnGetDateRanges
(
    @type char(1),
    @start datetime,
    @finish datetime
)
returns @ranges table(start datetime, finish datetime)
as 
begin

    declare @from datetime 
    declare @to datetime 
    set @from = @start 

    if @type = 'd'
    begin 
     set @to = dateadd(day, 1,
       convert
       ( datetime,
        cast(DatePart(d,@start) as varchar) + '/' + cast(DatePart(m,@start) as varchar) + '/' + cast(DatePart(yy,@start) as varchar),
        103
       )
      )
    end

    if @type = 'm'
    begin
     set @to = dateadd(month, 1, 
      convert
      ( 
       datetime,
       '1/' + cast(DatePart(m,@start) as varchar) + '/' + cast(DatePart(yy,@start) as varchar),
       103
      )
     )
    end 

    if @type = 'y'
    begin
     set @to = dateadd(year, 1, 
      convert
      ( 
       datetime,
       '1/1/' + cast(DatePart(yy,@start) as varchar),
       103
      )
     )
    end 

    while @to < @finish
    begin 
     insert @ranges values (@from, @to)
     set @from = @to 
     if @type = 'd'
      set @to = dateadd(day, 1, @to)
     if @type = 'm'
      set @to = dateadd(month, 1, @to)
     if @type = 'y'
      set @to = dateadd(year, 1, @to)
    end

    insert @ranges values (@from, @finish)

    return 
end

Answer 2

If you prefer a set-based solution, use a tactic like the one shown in the following link to produce a range of numeric values from x to y. Then, just join to it with DATEADD() and your own custom limits to create ranges of days, months, quarters, years, or whatever else. I find it helpful to have this range query as a view.

Generate Ranges In SQL

Answer 3

A static number table is useful, single column, say 8000 rows FROM 0 TO 7999

(Not checked)

DECLARE @Start smalldatetime, @End smalldatetime, @Diff int

SELECT @Start = '2006-01-01 11:10:00', @End = '2009-05-05 08:00:00', @diff = DATEDIFF(year,@start,@end)

SELECT
   DATEADD(year,N.Number,@Start)
FROM
   dbo.Number N
WHERE
   N.Number <= @diff

Answer 4

From a performance standpoint, you will not want to use a function to generate the date ranges. For each evaluation in the query ( @myDate > dbo.MyFunc() ), the function will have to execute fully. Your best bet is to build static numbers table.

Now on with the numbers tables....

This is a fast way to create a integers table. (Props to Jeff Moden for the Identity Trick)

 SELECT TOP 1000000
        IDENTITY(INT,1,1) as N
   INTO dbo.NumbersTable
   FROM Master.dbo.SysColumns 
        Master.dbo.SysColumns

Less than 2 seconds to populate 1000000 numbers in a table.

Now to address your problem, you will need to use this to build a table of dates. The example below will create a table with the zero hour (12AM) for each day starting from the @startDate

DECLARE @DaysFromStart int
DECLARE @StartDate datetime
SET @StartDate = '10/01/2008'

SET @ DaysFromStart  = (SELECT (DATEDIFF(dd,@StartDate,GETDATE()) + 1))

CREATE TABLE [dbo].[TableOfDates](
    [fld_date] [datetime] NOT NULL,
 CONSTRAINT [PK_TableOfDates] PRIMARY KEY CLUSTERED 
(
    [fld_date] ASC
)WITH FILLFACTOR = 99 ON [PRIMARY]
) ON [PRIMARY]


INSERT INTO
     dbo.TableOfDates
SELECT 
      DATEADD(dd,nums.n - @DaysFromStart ,CAST(FLOOR(CAST(GETDATE() as FLOAT)) as DateTime)) as FLD_Date
FROM #NumbersTable nums

SELECT MIN(FLD_Date) FROM dbo.TableOfDates
SELECT MAX(FLD_Date) FROM dbo.TableOfDates

Now with different combinations of DATEADD/DIFF, you should be able to create the static tables that you will need to do many date range queries efficiently.