SQL DATEADD

SQL DATEADD Function is used to add a specific interval to the existing date. For example, For every product order, we will save the Ordered Date, and we can use this Dateadd function to generate the expected delivery date.

SQL DATEADD Syntax

DATEADD (Datepart, Number, Date)

Datepart: It is the part of a given data to which we are going to add the Number argument of the SQL Server Dateadd function. The following table will display the list of available datepart arguments in the dateadd function.

DatepartAbbreviationsDescription
yearyy,yyyyThis will add a specified number of years (in the second argument)
quarterqq, qAdd the specified number of Quarters (in the second argument)
monthmm, mAdd Months
dayofyeardy, yThis argument will add a day of the year
daydd, dThis will add a specified number of days
weekwk, wwAdd the specified number of Weeks (in the second argument)
weekdaydw, wAdd Weekdays
hourhhIt will add Hours
minutemi, nIt adds the specified number of Minutes.
secondss, sAdd Seconds
millisecondmsThis dateadd argument will add Milliseconds
microsecondmcsAdd Microseconds
nanosecondnsAdd Nanoseconds

Date: Please specify the valid SQL Server date as the third argument, and it can be a column, expression, or any variable.

Number: Please specify the number you want to add for this SQL Server dateadd function.

  • If you specify the positive integer as the second argument (Number), it will add the specified number to the given date.
  • If you specify the Negative integer, the function will subtract that.
  • And, If you specify the Decimal number as the second argument (Number), this function will truncate the decimal values and add the specified number to the given datetime.

SQL DATEADD Positive integers

In this Dateadd example, we are going to use the positive integer as a second argument.

In this transact query, we declared one variable and assigned the date and time to that variable.

(year, 1, @dtVal) – this dateadd function will add 1 year to the given date. Next, (month, 1, @dtVal) add 1 month to the given date.

SQL Dateadd 1

Negative Integer in SQL Server DATEADD

In this Dateadd example, we use the Negative integer as a second argument.

SQL Server DATEADD 2

We asked to add 1 year to the given date, but the function subtracted 1 year from it. This is because the Negative value is used to subtract (year, -1, @dVl).

We asked the SQL Server Dateadd function to add 1 Month to the given date, but the function subtracted 1 Month from it. This is because the Negative value is used to subtract (month, -1, @dVl).

Decimal Values

In this Date Function example, we are going to use the decimal value as a second argument.

DECLARE @dtValue datetime2 = '2015-01-01 14:24:04.1234567'
SELECT 'YEAR' AS [D Part], DATEADD(year, 2.23, @dtValue) AS [New D T] 
UNION ALL
SELECT 'QUARTER', DATEADD(quarter, 3.98, @dtValue) 
UNION ALL
SELECT 'MONTH', DATEADD(month, -4.51, @dtValue)
Decimal Values 3

We asked the Dateadd function to add 2.23 years to the given date, but the function added 2 years only. This is because .23 values are truncated.

We asked to add 4.51 months to the given date, but the function subtracted 4 months from a given date. This is because the Negative value is used to subtract, and the .51 value is truncated.