SQL DATEADD Function

Syntax

 

 DATEADD (datepart , number , date )

 

SpecifyNumberThe time interval (signed integer) and the specifiedDateSpecifiedDatepartReturnsDate.

 

 

Parameters

Datepart

 Yes andNumberAddedDate. The following table lists all validDatepartParameters. The user-defined equivalent variable is invalid.

　　

 

Number

Is an expression that can be parsed into an int that is added to datepart of date. User-Defined variables are valid.

If you specify a decimal value, the decimal number is truncated without rounding.

Date

Is an expression that can be parsed to time, date, smalldatetime, datetime, datetime2, or datetimeoffset values. Date can be an expression, column expression, user-defined variable, or string text. If the expression is a string, it must be resolved to a datetime value. To avoid uncertainty, use a four-digit year.

Return type

The return data type of string text is datetime. If the number of decimal places in the string text in seconds exceeds three (. nnn) or contains the time zone offset, an error is thrown.

 

Return Value

 Datepart Parameters

　　Dayofyear,DayAndWeekdayReturns the same value.

EachDatepartReturn the same value.

If datepart is month and the month of date (January) is more than the number of days in the returned month (January), the day in date does not exist in the returned month (31 of February ), returns the last day of the month. For example, there are 30 days in February. Therefore, the following two statements return September 00:00:00. 000:

 

 SELECT DATEADD(month, 1, '2006-08-30')
SELECT DATEADD(month, 1, '2006-08-31')

 

Number Parameter NumberParameter cannot exceed Int. In the following statement, NumberThe parameter of exceeds IntThe value range is 1. The following error message is returned: "An Arithmetic overflow error occurs when the expression is converted to an int type ."

 SELECT DATEADD(year,2147483648, '2006-07-31');
SELECT DATEADD(year,-2147483649, '2006-07-31');

 

Date Parameter DateThe parameter cannot be added to a value out of its data range. In the following statement DateValue addition NumberThe value exceeds DateThe range of data types. The following error message is returned: "adding a value to the 'datetime' column causes overflow ."

 SELECT DATEADD(year,2147483647, '2006-07-31');
SELECT DATEADD(year,-2147483647, '2006-07-31');