DATEADD in Sql Server

Returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

Syntax

DATEADD (datepart , number , date )

Arguments

datepart

Is the part of date to which an integer number is added. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid.

datepart	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
microsecond	mcs
nanosecond	ns

number

Is an expression that can be resolved to an int that is added to a datepart of date. User-defined variables are valid.

If you specify a value with a decimal fraction, the fraction is truncated and not rounded.

date

Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime. To avoid ambiguity, use four-digit years. For information about two-digit years, see Configure the two digit year cutoff Server Configuration Option.

Return Types

The return data type is the data type of the date argument, except for string literals.

The return data type for a string literal is datetime. An error will be raised if the string literal seconds scale is more than three positions (.nnn) or contains the time zone offset part.

datepart Argument

dayofyear , day, and weekday return the same value.

Each datepart and its abbreviations return the same value.

If datepart is month and the date month has more days than the return month and the date day does not exist in the return month, the last day of the return month is returned. For example, September has 30 days; therefore, the two following statements return 2006-09-30 00:00:00.000:

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

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

The number argument cannot exceed the range of int. In the following statements, the argument for number exceeds the range of int by 1. The following error message is returned: "Msg 8115, Level 16, State 2, Line 1. Arithmetic overflow error converting expression to data type int."

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

The date argument cannot be incremented to a value outside the range of its data type. In the following statements, the number value that is added to the datevalue exceeds the range of the date data type. The following error message is returned: "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow."

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

The seconds part of a smalldatetime value is always 00. If date is smalldatetime, the following apply:

• If datepart is second and number is between -30 and +29, no addition is performed.
• If datepart is second and number is less than-30 or more than +29, addition is performed beginning at one minute.
• If datepart is millisecond and number is between -30001 and +29998, no addition is performed.
• If datepart is millisecond and number is less than -30001 or more than +29998, addition is performed beginning at one minute.

Remarks

DATEADD can be used in the SELECT <list>, WHERE, HAVING, GROUP BY and ORDER BY clauses.

Fractional Seconds Precision

Addition for a datepart of microsecond or nanosecond for date data types smalldatetime, date, and datetime is not allowed.

Milliseconds have a scale of 3 (.123), microseconds have a scale of 6 (.123456), And nanoseconds have a scale of 9 (.123456789). The time, datetime2, anddatetimeoffset data types have a maximum scale of 7 (.1234567). If datepart is nanosecond, number must be 100 before the fractional seconds of dateincrease. A number between 1 and 49 is rounded down to 0 and a number from 50 to 99 is rounded up to 100.

The following statements add a datepart of millisecond, microsecond, or nanosecond.

DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';
SELECT '1 millisecond', DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT '2 milliseconds', DATEADD(millisecond,2,@datetime2)
UNION ALL
SELECT '1 microsecond', DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT '2 microseconds', DATEADD(microsecond,2,@datetime2)
UNION ALL
SELECT '49 nanoseconds', DATEADD(nanosecond,49,@datetime2)
UNION ALL
SELECT '50 nanoseconds', DATEADD(nanosecond,50,@datetime2)
UNION ALL
SELECT '150 nanoseconds', DATEADD(nanosecond,150,@datetime2);

Here is the result set.

1 millisecond     2007-01-01 13:10:10.1121111
2 milliseconds    2007-01-01 13:10:10.1131111
1 microsecond     2007-01-01 13:10:10.1111121
2 microseconds    2007-01-01 13:10:10.1111131
49 nanoseconds    2007-01-01 13:10:10.1111111
50 nanoseconds    2007-01-01 13:10:10.1111112
150 nanoseconds   2007-01-01 13:10:10.1111113