• Messages 1-500
• Messages 501-1000
• Messages 1001-1500
• Messages 1501-2000
• Messages 2001-3000
• Messages 3001-4000
• Messages 4001-5000
• Messages 5001-6000
• Messages 6001-7000
• Messages 7001-7500
• Messages 7501-8000
• Messages 8001-8500
• Messages 8501-9000
• Messages 9001-9500
• Messages 9501-10000
• Messages 10001-10500
• Messages 10501-11000
• Messages 11001-11500
• Messages 11501-12000
• Messages 12001-13000
• Messages 13001-13500
• Messages 14001-14500

Server: Msg 155, Level 16, State 1, Line 1
'<Option>' is not a recognized DATEADD option.

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:

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())

• Frequently Asked Questions - SQL Server Error Messages
• Tips & Tricks - SQL Server Error Messages 1 to 500