Dec 20, 2023

Work dynamic with a built-in function DATEADD

Updated: Feb 28

Recently, my customer faced the need to calculate a point in time based on another date. In T-SQL, we can use it with the built-in function DATEADD. But in this case, he needs to make it dynamically. All function parameters change according to dynamic conditions in the process.

That can be a little bit complicated; the function doesn't accept the date part as a variable.

But before we jump in, let's see what the T-SQL built-in function DATEADD is:

Imagine you have a date and want to know what date it will be after (or was before) a certain amount of time has passed. So, DATEADD is your special tool! It can help you figure this out without scribbling all over your calendar.

Every tool has a user guide, and that's no exception:

Pick your starting point. Any date you can think of, for example, today or your birthday, or the launch date of your favorite movie.
Choose your direction. Do you want to move forward in time (add) or go back (subtract)? By the way, "forward" is the default direction, and it doesn't have to be specified.
Specify the number of steps to take. Tell the tool how much time you want to add or subtract!
Specify how big your steps are. Choose whether it's years, months, days, hours, or even seconds!

This is what it looks like:

DATEADD(“Steps size”, “direction” and “number of steps”, “starting point”)

Once you give DATEADD these instructions, it will do its magic and return you the new date you were looking for. It's like rewinding or fast-forwarding your calendar but with the precision of a computer!

Now, let's try some "real-world" examples:

If you give DATEADD to add 5 years to today's date, you'll find out what day it will be five years from now. Or, if you subtract 3 months from your birthday, the result will be your birthday if you were born 3 months earlier.

This is what our examples look like in practice:

DATEADD(YEAR, 5, GETDATE())
DATEADD(MONTH, -3, ‘1950-08-15’)

So, DATEADD is our handy helper for jumping between different dates and times in T-SQL!

Now, let's return to the first problem, which is dynamic parameters. So, yes as you already understand we can't pass parameters for the first position.

DECLARE
	@Datepart	NVARCHAR(16)	= N'YEAR',
	@Interval	INT				= 2

SELECT DATEADD(@Datepart, @Interval, GETDATE())

That will not work. The error will be

Msg 1023, Level 15, State 1, Line 15
Invalid parameter 1 specified for dateadd.

So, it's impossible, to make it dynamic with variables?

According to the Microsoft docs:

DATEADD does not accept user-defined variable equivalents for the datepart arguments

So we kind of stuck a work-around options only.

Solution 1 - Dynamic SQL:

Chain all parts of the function and variables as a text in one single string and execute it as dynamic SQL.

DECLARE
	@Datepart	NVARCHAR(16)	= N'YEAR',
	@Interval	INT				= 2,
	@StartDate	NVARCHAR(26)	= CONCAT(N'''', CONVERT(NVARCHAR(24), GETDATE(), 121), N''''),
	@SQL		NVARCHAR(MAX)	= N''

SET @SQL += CONCAT(N'SELECT DATEADD(', @Datepart, N',', @Interval, N',', @StartDate, ')');

EXEC (@SQL);

DECLARE
	@Datepart	NVARCHAR(16)	= N'DAY',
	@Interval	INT				= 123,
	@StartDate	NVARCHAR(26)	= CONCAT(N'''', CONVERT(NVARCHAR(24), '2023-12-20 12:00:12.167'), N''''),
	@SQL		NVARCHAR(MAX)	= N''

SET @SQL += CONCAT(N'SELECT DATEADD(', @Datepart, N',', @Interval, N',', @StartDate, ')')

EXEC (@SQL)

Solution 2 - Dynamic SQL: Using CASE expression! Prepare all possible variations of the DATEADD executions and reuse them as you need.

DECLARE
	@Datepart		NVARCHAR(16)	= N'YEAR',
	@Interval		INT				= 2,
	@StartDate		DATETIME		= GETDATE(),
	@NewDateTime	DATETIME

SET @StartDate = 
				CASE
					WHEN @Datepart IN ('year', 'yy', 'yyyy')		THEN DATEADD(YEAR, @Interval, @StartDate)
					WHEN @Datepart IN ('quarter', 'qq', 'q')		THEN DATEADD(QUARTER, @Interval, @StartDate)
					WHEN @Datepart IN ('month', 'mm', 'm')			THEN DATEADD(MONTH, @Interval, @StartDate)
					WHEN @Datepart IN ('dayofyear', 'dy', 'y')		THEN DATEADD(DAYOFYEAR, @Interval, @StartDate)
					WHEN @Datepart IN ('day', 'dd', 'd')			THEN DATEADD(DAY, @Interval, @StartDate)
					WHEN @Datepart IN ('week', 'wk', 'ww')			THEN DATEADD(WEEK, @Interval, @StartDate)
					WHEN @Datepart IN ('weekday', 'dw', 'w')		THEN DATEADD(WEEKDAY, @Interval, @StartDate)
					WHEN @Datepart IN ('hour', 'hh')				THEN DATEADD(HOUR, @Interval, @StartDate)
					WHEN @Datepart IN ('minute', 'mi', 'n')			THEN DATEADD(MINUTE, @Interval, @StartDate)
					WHEN @Datepart IN ('second', 'ss', 's')			THEN DATEADD(SECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('millisecond', 'ms')			THEN DATEADD(MILLISECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('microsecond', 'mcs')		THEN DATEADD(MICROSECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('nanosecond', 'ns')			THEN DATEADD(NANOSECOND, @Interval, @StartDate)
				END

SELECT @StartDate

DECLARE
	@Datepart		NVARCHAR(16)	= N'DAY',
	@Interval		INT				= 123,
	@StartDate		DATETIME		= '2023-12-20 12:00:12.167',
	@NewDateTime	DATETIME

SET @StartDate = 
				CASE
					WHEN @Datepart IN ('year', 'yy', 'yyyy')		THEN DATEADD(YEAR, @Interval, @StartDate)
					WHEN @Datepart IN ('quarter', 'qq', 'q')		THEN DATEADD(QUARTER, @Interval, @StartDate)
					WHEN @Datepart IN ('month', 'mm', 'm')			THEN DATEADD(MONTH, @Interval, @StartDate)
					WHEN @Datepart IN ('dayofyear', 'dy', 'y')		THEN DATEADD(DAYOFYEAR, @Interval, @StartDate)
					WHEN @Datepart IN ('day', 'dd', 'd')			THEN DATEADD(DAY, @Interval, @StartDate)
					WHEN @Datepart IN ('week', 'wk', 'ww')			THEN DATEADD(WEEK, @Interval, @StartDate)
					WHEN @Datepart IN ('weekday', 'dw', 'w')		THEN DATEADD(WEEKDAY, @Interval, @StartDate)
					WHEN @Datepart IN ('hour', 'hh')				THEN DATEADD(HOUR, @Interval, @StartDate)
					WHEN @Datepart IN ('minute', 'mi', 'n')			THEN DATEADD(MINUTE, @Interval, @StartDate)
					WHEN @Datepart IN ('second', 'ss', 's')			THEN DATEADD(SECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('millisecond', 'ms')			THEN DATEADD(MILLISECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('microsecond', 'mcs')		THEN DATEADD(MICROSECOND, @Interval, @StartDate)
					WHEN @Datepart IN ('nanosecond', 'ns')			THEN DATEADD(NANOSECOND, @Interval, @StartDate)
				END

SELECT @StartDate

That will examine a list of conditions and returns one of several possible result expressions.

Additionally, this solution can be capsulated as a scalar function and reused to your needs with minimal space using in the code.