SQL DATEADD

SQL DATEADD Function is used to add specific date interval to the existing date. For example, For every product order we will save the Ordered Date and we can use this SQL 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 add the Number argument of SQL Dateadd function. The following table will display the list of available datepart argument in SQL Server dateadd function.

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

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

Number: Please specify the number you want to add.

  • 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 DATEADD function will subtract that.
  • And, If you specify the Decimal number as the second argument (Number), the DATEADD function will truncate the decimal values and add specified number to the given datetime.

Positive integer in SQL DATEADD

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

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

(year, 1, @DateAdd) – the SQL Dateadd function will add 1 year to the given date. Next, (month, 1, @DateAdd) adds 1 month to the given date.

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567'
SELECT 'YEAR' AS [DatePart], DATEADD(year, 1, @DateAdd) AS [New Date] 
UNION ALL
SELECT 'QUARTER', DATEADD(quarter,1,@DateAdd) 
UNION ALL
SELECT 'MONTH', DATEADD(month, 1, @DateAdd) 
UNION ALL
SELECT 'DAYOFYEAR', DATEADD(dayofyear, 1, @DateAdd) 
UNION ALL
SELECT 'DAY', DATEADD(day, 1, @DateAdd) 
UNION ALL
SELECT 'WEEK', DATEADD(week, 1, @DateAdd) 
UNION ALL
SELECT 'WEEKDAY', DATEADD(weekday,1,@DateAdd) 
UNION ALL
SELECT 'HOUR', DATEADD (hour, 1, @DateAdd) 
UNION ALL
SELECT 'MINUTE', DATEADD(minute, 1, @DateAdd) 
UNION ALL
SELECT 'SECOND', DATEADD(second, 16, @DateAdd) 
UNION ALL
SELECT 'MILLISECOND', DATEADD(millisecond, 1, @DateAdd) 
UNION ALL
SELECT 'MICROSECOND', DATEADD(microsecond, 50, @DateAdd) 
UNION ALL
SELECT 'NANOSECOND',DATEADD(nanosecond,500,@DateAdd)
SQL Dateadd 1

Negative Integer in SQL DATEADD

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

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567'
SELECT 'YEAR' AS [DatePart], DATEADD(year, -1, @DateAdd) AS [New Date] 
UNION ALL
SELECT 'QUARTER', DATEADD(quarter, -1, @DateAdd) 
UNION ALL
SELECT 'MONTH', DATEADD(month, -1, @DateAdd) 
UNION ALL
SELECT 'DAYOFYEAR', DATEADD(dayofyear, -1, @DateAdd) 
UNION ALL
SELECT 'DAY', DATEADD(day, -1, @DateAdd) 
UNION ALL
SELECT 'WEEK', DATEADD(week, -1, @DateAdd) 
UNION ALL
SELECT 'WEEKDAY', DATEADD(weekday, -1, @DateAdd) 
UNION ALL
SELECT 'HOUR', DATEADD (hour, -1, @DateAdd) 
UNION ALL
SELECT 'MINUTE', DATEADD(minute, -1, @DateAdd) 
UNION ALL
SELECT 'SECOND', DATEADD(second, -14, @DateAdd)
SQL Dateadd 2

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

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

Decimal Values in DATEADD

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

DECLARE @DateAdd datetime2 = '2015-01-01 14:24:04.1234567'
SELECT 'YEAR' AS [DatePart], DATEADD(year, 2.23, @DateAdd) AS [New Date] 
UNION ALL
SELECT 'QUARTER', DATEADD(quarter, 3.98, @DateAdd) 
UNION ALL
SELECT 'MONTH', DATEADD(month, -4.51, @DateAdd)
SQL Dateadd 3

We asked the SQL 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

SELECT 'YEAR', DATEADD(year, 2.23, @DateAdd)

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

SELECT 'MONTH', DATEADD(month, -4.51, @DateAdd)