Date ranges in T/SQL

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?

From stackoverflow Sam Saffron

• 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
  

  From Sam Saffron

• 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

  Sam Saffron : That could work, however I find it a little less elegant than the table function solution for mssql. If I really needed something that works across lots of different dbs then the set based option may be the only choice.
  From Pittsburgh DBA

• 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
  

  From gbn

• 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.

  From StingyJack