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