I'm trying to use a variable value for the 'interval' in the DATEADD built-in function in t-sql. However, this returns an error: 'Invalid parameter 1 specified for dateadd'. Is there a way in t-sql to add this interval input parameter, either via a variable as per the below, or via a table column / temporary table column?
DECLARE @start datetime = '20211122',
@freq int = 7,
@freqdaypart nvarchar(20) = N'day',
@now datetime = GETDATE()
;WITH cte(st) AS (
SELECT @start st
UNION ALL
SELECT DATEADD(@freqdaypart, @freq, st)
FROM cte
WHERE DATEADD(@freqdaypart, @freq, st) < @now
)
SELECT *
FROM cte
OPTION(MAXRECURSION 0)
My fallback is to do something like the below but I'd like to avoid this if possible..
DECLARE @start datetime = '20211122',
@freq int = 7,
@freqdaypart nvarchar(20) = N'day',
@now datetime = GETDATE()
IF @freqdaypart = 'day'
BEGIN
;WITH cte(st) AS (
SELECT @start st
UNION ALL
SELECT DATEADD(DAY, @freq, st)
FROM cte
WHERE DATEADD(DAY, @freq, st) < @now
)
SELECT *
FROM cte
OPTION(MAXRECURSION 0)
END
IF @freqdaypart = 'month'
...
ps. the above is simplified, in the actual use I intend on retrieving the interval ('day' or 'month') from a database table and process several rows at a time.
With a special tip of the hat to Jonathan Roberts of on SQLServerCentral.com, this should do it for you...
CREATE OR ALTER FUNCTION dbo.DateRange
/**********************************************************************************************************************
Purpose:
Given a start date, an end date, a "date part", and an increment, return a sequence of rows for the given date part
according to the dates and the increment.
-----------------------------------------------------------------------------------------------------------------------
Parameters:
@StartDate: Start date of the series - Required - May be greater than @EndDate
@EndDate : End date of the series - Required - May be less than @StartDate
@DatePart : The time unit for @interval - Optional (Default = 'dd')
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 - Optional (Default = 1)
------------
Return: DT as a DATETIME2(7) Column of dates and times
-----------------------------------------------------------------------------------------------------------------------
Sample Calls:
--===== Return a row for every other second in the date range.
SELECT * FROM dbo.DateRange('2011-01-01 12:24:35', '2011-02-01 12:24:35', 'ss', 2)
;
--===== Return a coumt of rows for every millisecond in the date range (Default increment = 1)
-- A simple DATEDIFF would do the trick but it does demonstate the scope of the function.
SELECT COUNT(*) FROM dbo.DateRange('2018-01-01 00:00:00', '2018-01-25 20:31:23.646', 'ms', default)
;
--===== Return a row for each date in the date range (1st default is "dd" and second default is 1)
SELECT * FROM dbo.DateRange('2011-01-01', '2012-02-03', default, default)
;
--===== Since @StartDate > @EndDate, this returns a row for every 7 days counting backwards.
SELECT * FROM dbo.DateRange('2012-02-03', '2011-01-01', 'dd', 7)
;
--===== This demonstrates how you can do calculations in the SELECT list.
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)
;
--===== See the following link for an example that seriously simplified the task at hand as well as making it easy to
-- add other "granularities" (date parts).
https://www.sqlservercentral.com/forums/topic/tsql-create-dynamic-partition-datarange#post-3944333
-----------------------------------------------------------------------------------------------------------------------
Revision History:
Rev 00 - 19 Aug 2019 - Jonathan Roberts
- Initial Release
- https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
Rev 01 - 25 Oct 2021 - Jeff Moden.
- Apply personal code standards and additional information in the documentation
- Add link to extreme sample usage where the function greatly simplified the task at hand as well as making it
easy to add other "granularities".
- Add WITH SCHEMABINDING.
- Code reduction by moving /@Interval from each THEN to END of CASE.
*********************************************************************************************************************/
--===== Function I/O
(
@StartDate DATETIME2
,@EndDate DATETIME2
,@DatePart VARCHAR(3) = 'dd'
,@Interval INT = 1
)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN WITH
--===== 16 digit base for InLine Tally Table
H(Z) AS (SELECT 0 FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))H0(Z)),
--===== InLine Tally Table returns rows starting at 0 based on the DATEPART divided by the interval.
T(N) AS (SELECT TOP(ABS(
CASE @DatePart
WHEN 'ns' THEN DATEDIFF(ns, @EndDate,@StartDate)
WHEN 'mcs' THEN DATEDIFF(mcs,@EndDate,@StartDate)
WHEN 'ms' THEN DATEDIFF(ms, @EndDate,@StartDate)
WHEN 'ss' THEN DATEDIFF(ss, @EndDate,@StartDate)
WHEN 'mi' THEN DATEDIFF(mi, @EndDate,@StartDate)
WHEN 'hh' THEN DATEDIFF(hh, @EndDate,@StartDate)
WHEN 'dd' THEN DATEDIFF(dd, @EndDate,@StartDate)
WHEN 'ww' THEN DATEDIFF(ww, @EndDate,@StartDate)
WHEN 'mm' THEN DATEDIFF(mm, @EndDate,@StartDate)
WHEN 'qq' THEN DATEDIFF(qq, @EndDate,@StartDate)
WHEN 'yy' THEN DATEDIFF(yy, @EndDate,@StartDate)
ELSE DATEDIFF(dd --Ensures we get a correct positive value if dates are reversed
,IIF(@StartDate < @EndDate, @StartDate, @EndDate)
,IIF(@StartDate < @EndDate, @EndDate, @StartDate)
)
END/@Interval) + 1) --End of TOP(ABS, Adds 1 interval to makeup for subtraction of INTs
N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 --So we start at ZERO
FROM H a,H b,H c,H d,H e,H f,H g,H h -- A maximum of 16^8 (or 2^32) rows can be returned
)
SELECT DT =
CASE @DatePart
WHEN 'ns' THEN DATEADD(ns, c.CountAmount,@StartDate)
WHEN 'mcs' THEN DATEADD(mcs,c.CountAmount,@StartDate)
WHEN 'ms' THEN DATEADD(ms, c.CountAmount,@StartDate)
WHEN 'ss' THEN DATEADD(ss, c.CountAmount,@StartDate)
WHEN 'mi' THEN DATEADD(mi, c.CountAmount,@StartDate)
WHEN 'hh' THEN DATEADD(hh, c.CountAmount,@StartDate)
WHEN 'dd' THEN DATEADD(dd, c.CountAmount,@StartDate)
WHEN 'ww' THEN DATEADD(ww, c.CountAmount,@StartDate)
WHEN 'mm' THEN DATEADD(mm, c.CountAmount,@StartDate)
WHEN 'qq' THEN DATEADD(qq, c.CountAmount,@StartDate)
WHEN 'yy' THEN DATEADD(yy, c.CountAmount,@StartDate)
ELSE DATEADD(dd, c.CountAmount,@StartDate)
END
FROM T t
CROSS APPLY(VALUES(IIF(@StartDate<@EndDate
,@Interval*(t.N) --Count Up
,@Interval*(-t.N) --Count Down
)))c(CountAmount)
;