Fix Error 155 “‘DAYS’ is not a recognized dateadd option” in SQL Server

If you’re getting an error that reads something like “‘DAYS’ is not a recognized dateadd option” in SQL Server, it’s because you’re using the DATEADD() function with an invalid datepart argument.

This often happens when you use a plural form of the argument. For example, DAYS instead of DAY. Or HOURS instead of HOUR.

The easiest way to fix this is to provide a valid datepart argument.

Example of Error

Here’s an example of code that results in the error:

DECLARE @d DATE = '2030-03-24';
SELECT DATEADD(DAYS, 2, @d);

Output:

Msg 155, Level 15, State 1, Line 2
'DAYS' is not a recognized dateadd option.

The error message is self-explanatory: DAYS is not a recognized option when using the DATEADD() function.

Solution

Fortunately, the solution is easy: Use the right option.

In our case, we simply remove the S from DAYS so that the argument is provided in singular form:

DECLARE @d DATE = '2030-03-24';
SELECT DATEADD(DAY, 2, @d);

Output:

2030-03-26

SQL Server also accepts the abbreviated form, so we can do the following:

DECLARE @d DATE = '2030-03-24';
SELECT 
    DATEADD(D, 2, @d) AS [D],
    DATEADD(DD, 2, @d) AS [DD];

Output:

D                         DD                      
-------------  -------------
2030-03-26  2030-03-26

It’s not case sensitive, so dd will work just the same as DD.

Supported Options

Here are the datepart options supported by the DATEADD() function in SQL Server:

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns