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 232) to 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 ASC2. 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.DateUses
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 