A DateRange Table Valued function

,

Introduction

SQL developers often need to make use of a Calendar table to get date ranges for their queries, this often involves creating a permanent table on the database and populating it with years of data then having to remember to update the Calender table to add new dates and maybe delete old dates as time expires to keep the size of the table under control. This function can be a replacement for a simple calendar table and also has other uses for selecting a range of any datetime parameter.

This is a fast table-valued function that will return a table of datetime2 rows in any date range desired.

The function has an inline tally table to generate a row for each date value required, it  will return enough rows (up to 232to cope with any number the SQL Server DATEDIFF function returns (up to 231-1), it is a fast function though you might be waiting a while for it to generate that many rows!

Syntax

SELECT value 
  FROM dbo.DateRange(startdate, enddate, datepart, interval)

Arguments

@StartDate - parameter indicating the value of the first row returned.

This can be any of data types:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

@EndDate - parameter indicating the value of the last row returned. note if the datepart is not a perfect divider it may return a higher or lower value depending on the datepart chosen and the interval.

This can be any of:

  • date
  • datetime
  • datetimeoffset
  • datetime2
  • smalldatetime
  • time

@DatePart any of 'ns' ,'mcs', 'ms', 'ss' , 'mi', 'hh', 'dd', 'ww', 'mm', 'qq', 'yy' (see https://docs.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql)

Note: this parameter must be enclosed in single quotes (unlike datepart parameter to the DATEDIFF function).

Return Types

Returns a single-column table of type datetime2 with column-name [value].

Usage

The function is an inline table-valued function (or sometimes called a parameterised view) so it can be used just like a view or a table but needs parameters. It generates a single-column table of datetime2 with rows differing by any multiple of any of the dateparts that SQL Server provides.

1. Used to get a list of times of all seconds in a given day:

SELECT Value
  FROM dbo.DateRange('2018-09-20','2018-09-21','ss',1)

Will return 86,401 rows of every second between 2018-09-20 00:00:00 and 2018-09-21 00:00:00

If you enter a startdate that's greater than the enddate it will return the list in descending order, of course as it returns a table you can sort it how you like with an ORDER BY.

SELECT Value 
  FROM dbo.DateRange('2018-09-21','2018-09-20','mi',10)

Will return times in reverse order of 10 seconds intervals from '2018-09-21' to '2018-09-20'

The return order can be overridden:

SELECT value 
  FROM dbo.DateRange('2018-09-21','2018-09-20','mi',10)
 ORDER BY value ASC

2. Used to get a contiguous list of dates between two date variables for left joining on a table that has missing dates (if you want all dates to be shown whether or not the joining table has them.

SELECT dr.value, SUM(s.SalesRevenue) Revenue
  FROM dbo.DateRange('2018-09-01','2018-09-02','dd',1) dr
  LEFT JOIN dbo.Sales s on s.Date = dr.Date
 GROUP BY dr.Date
 ORDER BY dr.Date

Uses

There are many uses of this function, it won't return business or national specific dates like Bank Holiday dates and business financial dates but it can be used instead of a simple calendar table. It will return a table with a range of datetimes for any datepart (or muliple of) that SQL Server provides.Its uses include replacing a simple calendar table, to select a full range of times when some of the times are not present of the table being queried.

SELECT dr1.Value Date, DATENAME(weekday,dr1.Value),x.time
  FROM [dbo].[DateRange] ('20181001','20201101','dd',1) dr1
 CROSS APPLY(SELECT CONVERT(time,[Value]) time FROM [dbo].[DateRange] ('20181001','20181002','hh',1) dr2  WHERE convert(time,dr2.Value) between convert(time,'07:00') AND convert(time,'16:00')) x
 WHERE DATEPART(dw,dr1.Value) NOT IN (1,2)
 ORDER BY 1,3
IF OBJECT_ID('[dbo].[DateRange]','IF') IS NULL BEGIN
    PRINT 'CREATE FUNCTION [dbo].[DateRange]' 
    EXEC ('CREATE FUNCTION [dbo].[DateRange] () RETURNS TABLE AS RETURN SELECT 1 X') 
END
GO 
/*-- **********************************************************************
-- FUNCTION: DateRange
-- Returns a table of datetime values based on the parameters
-- Parameters:  
-- @StartDate    :Start date of the series 
-- @EndDate      :End date of the series 
-- @DatePart :The time unit for @interval
--     ns    : nanoseconds 
--     mcs   : microseconds 
--     ms    : milliseconds 
--     ss    : seconds
--     mi    : minutes
--     hh    : hours
--     dd    : days
--     ww    : weeks
--     mm    : months
--     qq    : quarters
--     yy    : years
-- @Interval :The number of dateparts between each value returned
--
-- Sample Calls:
--     SELECT * FROM [dbo].[DateRange]('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
--     SELECT COUNT(*) FROM [dbo].[DateRange]('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
--     SELECT * FROM [dbo].[DateRange]('2011-01-01', '2012-02-03', default, default)
--     SELECT * FROM [dbo].[DateRange]('2012-02-03', '2011-01-01', 'dd', 7)
--     SELECT DATEDIFF(ns,'2018-01-01 00:00:00.000', value),Value,* FROM [dbo].[DateRange]('2018-01-01 00:00:00.000', '2018-01-01 00:00:00.00001', 'ns', 100)
-- **********************************************************************/  
ALTER FUNCTION [dbo].[DateRange] 
(
    @StartDate datetime2, 
    @EndDate   datetime2, 
    @DatePart  nvarchar(3)='dd', 
    @Interval  int=1
)
RETURNS TABLE AS RETURN 
  WITH A(A) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
       B(RowNum) AS (SELECT TOP(ABS(CASE @DatePart
                                        WHEN 'ns'  THEN DATEDIFF(ns, @EndDate, @StartDate)/@Interval
                                        WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate, @StartDate)/@Interval
                                        WHEN 'ms'  THEN DATEDIFF(ms, @EndDate, @StartDate)/@Interval
                                        WHEN 'ss'  THEN DATEDIFF(ss, @EndDate, @StartDate)/@Interval
                                        WHEN 'mi'  THEN DATEDIFF(mi, @EndDate, @StartDate)/@Interval
                                        WHEN 'hh'  THEN DATEDIFF(hh, @EndDate, @StartDate)/@Interval
                                        WHEN 'dd'  THEN DATEDIFF(dd, @EndDate, @StartDate)/@Interval
                                        WHEN 'ww'  THEN DATEDIFF(ww, @EndDate, @StartDate)/@Interval
                                        WHEN 'mm'  THEN DATEDIFF(mm, @EndDate, @StartDate)/@Interval
                                        WHEN 'qq'  THEN DATEDIFF(qq, @EndDate, @StartDate)/@Interval
                                        WHEN 'yy'  THEN DATEDIFF(yy, @EndDate, @StartDate)/@Interval
                                        ELSE DATEDIFF(dd, IIF(@StartDate < @EndDate, @StartDate, @EndDate), IIF(@StartDate < @EndDate, @EndDate, @StartDate))/@Interval
                                    END) + 1)
                            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
                       FROM A A, A B, A C, A D, A E, A F, A G, A H)   -- A maximum of 16^8 (or 2^32) rows could be returned from this inline tally
  SELECT CASE @DatePart            
              WHEN 'ns'  THEN DATEADD(ns, T.AddAmount, @StartDate)
              WHEN 'mcs' THEN DATEADD(mcs,T.AddAmount, @StartDate)
              WHEN 'ms'  THEN DATEADD(ms, T.AddAmount, @StartDate)
              WHEN 'ss'  THEN DATEADD(ss, T.AddAmount, @StartDate)
              WHEN 'mi'  THEN DATEADD(mi, T.AddAmount, @StartDate)
              WHEN 'hh'  THEN DATEADD(hh, T.AddAmount, @StartDate)
              WHEN 'dd'  THEN DATEADD(dd, T.AddAmount, @StartDate)
              WHEN 'ww'  THEN DATEADD(ww, T.AddAmount, @StartDate)
              WHEN 'mm'  THEN DATEADD(mm, T.AddAmount, @StartDate)
              WHEN 'qq'  THEN DATEADD(qq, T.AddAmount, @StartDate)
              WHEN 'yy'  THEN DATEADD(yy, T.AddAmount, @StartDate)
              ELSE            DATEADD(dd, T.AddAmount, @StartDate)
          END [Value]
    FROM B
   CROSS APPLY(VALUES (IIF(@StartDate<@EndDate, @interval*RowNum, @interval*-RowNum))) T(AddAmount)
GO

Rate

5 (5)

Share

Share

Rate

5 (5)