Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Practice Test
Tip of the Day : SQL Server Database Design - Internet Movie Database (IMDb)
Error Messages
Home > SQL Server Error Messages > Msg 155 - '<Option>' is not a recognized DATEADD option.
SQL Server Error Messages - Msg 155 - '<Option>' is not a recognized DATEADD option.

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())
Related Articles :