SQL Server Error Messages - Msg 155¶
Error Message¶
Server: Msg 155, Level 16, State 1, Line 1
'<Option>' is not a recognized DATEADD option.
Causes¶
The DATEADD date function returns a new DATETIME (or SMALLDATETIME) value based on adding an interval to the specified date. The syntax of the DATEADD date function is as follows:
DATEADD ( datepart, number, date )
The datepart parameter specifies on which part of the date to return a new value. The number parameter is the value used to increment datepart. If a non-integer value is specified in the number parameter, the fractional part of the value is discarded. Lastly, the date parameter is an expression that returns a DATETIME or SMALLDATETIME value, or a character string in a date format.
This error message will be encountered if an invalid option is specified in the datepart parameter. Here are a few examples on how the error will be generated:
-- Trying to add 10 minutes to the Current System Date and Time
SELECT DATEADD(MIN, 10, GETDATE())
Msg 155, Level 15, State 1, Line 1
'MIN' is not a recognized dateadd option.
-- Trying to add 10 year to the Current System Date and Time
SELECT DATEADD(YR, 5, GETDATE())
Msg 155, Level 15, State 1, Line 1
'YR' is not a recognized dateadd option.
-- Trying to add 4 quarters to the Current System Date and Time
SELECT DATEADD(QTR, 4, GETDATE())
Msg 155, Level 15, State 1, Line 1
'QTR' is not a recognized dateadd option.
-- Trying to add 12 hours to the Current System Date and Time
SELECT DATEADD(H, 12, GETDATE())
Msg 155, Level 15, State 1, Line 1
'H' is not a recognized dateadd option.
-- Trying to add 30 days to the Current System Date and Time
SELECT DATEDIFF(DIA, 30, GETDATE())
Msg 155, Level 15, State 1, Line 1
'DIA' is not a recognized dateadd option.
Solution / Work Around¶
As the message suggests, a valid datepart option must be specified to the DATEADD date function. The following lists the valid datepart values and abbreviations recognized by SQL Server:
| Date Part | 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 |
Using the sample statements earlier, here’s the correct way of calling the DATEADD date function together with the correct datepart values:
-- Trying to add 10 minutes to the Current System Date and Time
SELECT DATEADD(MI, 10, GETDATE())
-- Trying to add 10 year to the Current System Date and Time
SELECT DATEADD(YY, 5, GETDATE())
-- Trying to add 4 quarters to the Current System Date and Time
SELECT DATEADD(Q, 4, GETDATE())
-- Trying to add 12 hours to the Current System Date and Time
SELECT DATEADD(HH, 12, GETDATE())
-- Trying to add 30 days to the Current System Date and Time
SELECT DATEDIFF(DD, 30, GETDATE())