SQL Server Error Messages - Msg 174¶

Error Message¶

Server: Msg 174, Level 15, State 1, Line 1
The dateadd function requires 3 argument(s).

Causes¶

The DATEADD date function returns a specified date with the specified number interval (signed integer) added to a specified datepart of the input date. The return data type is the data type of the input date parameter, except for string literals where the return data type will become DATETIME. The syntax of the DATEADD built-in date function is as follows:

DATEADD ( <datepart>, <number>, <date> )

All three parameters of the DATEADD function are required. The <datepart> parameter is the part of the <date> parameter to which an integer <number> is added. The second parameter, the <number>, is an expression that can be resolved to an INT data type that is added to a <datepart> of the <date> parameter. User-defined variables are allowed to be passed to this parameter. If a value with a decimal fraction is passed to this parameter, the fraction part is truncated and not rounded. Lastly, the <date> parameter is an expression that can be resolved to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET data type value. The <date> parameter can be an expression, column expression, user-defined variable, or string literal. If a string literal is passed to this parameter, it must resolve to a DATETIME data type.

Given the definition of a DATEADD date function as well as the parameters it requires, calling the DATEADD function without specifying one of the required parameters will generate this error. Each parameter or argument doesn’t have a default value that it will use when a value is not specified.

Here are a few examples on how the error is generated:

SELECT DATEADD(DD, 10) -– Missing Date Parameter

Msg 174, Level 15, State 1, Line 1
The dateadd function requires 3 argument(s).

Passing a different value for each parameter not expected by the DATEADD function will generate a different error message:

SELECT DATEADD('DD', 10, GETDATE()) -— DatePart Parameter in Single Quotes

Msg 1023, Level 15, State 1, Line 1
Invalid parameter 1 specified for dateadd.

SELECT DATEADD(MIN, 10, GETDATE()) -- Invalid DatePart Value

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

SELECT DATEADD(DD, 1, '2012/04/31') -- Invalid Date

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Solution / Work Around¶

As the error message suggests, make sure that when using the DATEADD built-in date function, all 3 arguments or parameters are supplied.

Here are a few examples on the uses of the DATEADD date function:

-- Return the Date Part of a DateTime Value
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)

-- Return the First Day of the Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0)
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)
SELECT DATEADD(YY, DATEDIFF(YY, 0, GETDATE()), 0)

-- Return the First Day of the Following Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0)
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) + 1, 0)
SELECT DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0)

-- Return the Last Day of the Month, Quarter and Year
SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) - 1
SELECT DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()) + 1, 0) - 1
SELECT DATEADD(YY, DATEDIFF(YY, 0, GETDATE()) + 1, 0) - 1