DATEADD SQL Function Tutorial [Practical Examples]

Overview of DATEADD SQL Function

DATEADD  is a SQL date function that is used for manipulating DateTime datatype values, DATEADD function is used to add  or subtract the specific numerical value to a specified datepart which can be a day, month,year, hour, or minute in an input date value, and returns modified date value

 

DATEADD SQL Function Syntax

DATEADD (datepart, number, input_date)

Here,

• datepart: It is a part of the date to which DATEADD adds an integer number.
• number: It is an integer number that will add to a datepart of the input date. DATEADD accepts user-defined variable values for numbers. DATEADD will truncate a specified number value that has a decimal fraction. It will not round the number value in this situation.
• input_date: It is a date datatype value or a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a DateTime.

Using four-digit years to avoid ambiguity issues, DATEADD function accepts the date value that can resolve to one of the following values:

• Date
• DateTime
• Datetimeoffset
• Datetime2
• Smalldatetime
• Time

 

Different datepart arguments

This table lists all valid datepart arguments

 

Return Value of datepart argument

dayofyear, day, and weekday return the same value

Each datepart and its abbreviations return the same value

If the following are true:

• datepart is month
• the datemonth has more days than the return month
• the dateday does not exist in the return month

 

Return types of DATEADD Function

The return value data type for this method is dynamic, The return type depends on the argument supplied for a date.

• If the value for a date is a string literal date, DATEADD returns a DateTime value.
• If another valid input data type is supplied for a date, DATEADD returns the same data type.
• DATEADD raises an error if the string literal seconds scale exceeds three decimal place positions (.nnn) or if the string literal contains the time zone offset part

 

Example-1: DATEADD SQL to add days in the date

Write SQL Query to add 15 days to specific constant date value 04-04-2021

SELECT DATEADD(dd, 15, '2021/04/04') AS 'NEW DATE'

• In the above query, the DATEADD SQL function is used to add 15 days to the inputted date 04-04-2021
• ‘dd’ is given as the date_part which indicates that the number argument value 15 will be added to the days part of the input date
•  the third argument value is date 4-04-2021 from which 4 is the daypart in which number 15 will be added

OUTPUT:

 

Example-2: DATEADD  SQL to subtract days from date

To subtract the days from the date we need to give a negative number value argument in DATEADD SQL function

Write SQL Query to subtract 10 days from the given date 15-02-2001

SELECT DATEADD(dd, - 10, '2001/02/15') AS 'NEW DATE'

• In this query, SQL DATEADD function is used to manipulate date value in the negative direction by subtracting the number of days from the day part of the input date value
• In the number argument of DATEADD function, the negative value of 10 is given to subtract the days from date 15-02-2001

OUTPUT:

 

Example-3: DATEADD SQL to add years to the date

We can use ‘YYYY’ or ‘YY’ as the date_part value to add years in the input date argument

Write SQL Query to add two years to the date 02-02-2003 using DATEADD function

SELECT DATEADD(YY, 2, '2003/02/02') AS 'NEW DATE'

• In the above query, the SQL DATEADD function used to add two years to the specified input date 02-02-2003
• In the datepart, ‘YY’ argument value is given for YEAR and in the number argument positive integer 2 is given for adding in the years

OUTPUT:

 

Example-4: DATEADD  SQL to subtract years from the date

Write SQL Query to subtract three years from the given input date ’03-03-2010’

SELECT DATEADD(YY, - 3, '2010/03/03') AS 'NEW DATE'

• In the above query, the DATEADD SQL function is used to subtract a year from the year part of the input date
• Negative value -3 is given as the value of number argument to perform subtraction

OUTPUT:

 

Example-5: DATEADD SQL to add or subtract months to the date

Add four months to the given input date ‘ 01-03-2005’

SELECT DATEADD(MM, 5, '2005/03/01') AS 'NEW DATE'

In the query, ‘MM’ is given in as the date_part argument value which means number argument value 5 is added to the month 04

OUTPUT:

 

Example-6: DATEADD SQL to add or subtract hours value to the input date

‘HH’ is used as the value of date_part to add or subtract hours from the given DateTime value

Write SQL Query to add 5 hours to the given DateTime value 01-05-2005 05:50:30  using DATEADD SQL

SELECT DATEADD(HH, 5, '2005/03/01 05:50:30 ') AS 'NEW DATE'

• In this query, DATEADD SQL is used to add value to the hours of datetime input value
• ‘HH’ is given as the datepart value to do the manipulation in the hour value
• to subtract hours from the given input date value, we need to give number value in negative

OUTPUT:

 

Example-7: DATEADD SQL to add or subtract minute’s value to the input date

‘Mi’ is used as the date_part value to add or subtract minutes from the given date

Write SQL Query to subtract 15 minutes to the given DateTime value 01-05-2005 05:50:30

SELECT DATEADD(Mi, -15, '2005/03/01 05:50:30 ') AS 'NEW TIME'

• In the above query, DATEADD function is used to subtract minutes from the given DateTime value
• In date argument value 05 hours 50 minute and 30 seconds is given after adding -15, it will subtract 15 from 50 and the resulting value will be 05 hours 35 minute and 30 second

OUTPUT:

 

Example-8: DATEADD  to add the Quarter value to the date

‘QQ’ is used as the date_part to add the Quarter value to the given date

Write SQL Query to add the Quarter value to the given date 10-03-2005

SELECT DATEADD(QQ, 1, '2005/03/10 ') AS 'NEW QUATERED DATE'

• In this query, DATEADD SQL is used to add the Quarter value 1 that means 3 months to the given date 10-03-2005
• ‘QQ’ is given as the date_part value to indicate that number value will be added as the Quarter value to the month

OUTPUT:

 

Example-9: DATEADD  with Invalid date input

The input_date argument must be of type DateTime, date, or the constant value must be in the format of YYYY-MM-DD, if any other type of value is given as the argument in the DATEADD SQL function it shows the error message ‘Your entry cannot be converted to a valid date-time value’ in the SQL Server

Write SQL Query to add 2 years to the 20030303 value

SELECT DATEADD(YYYY, 3, '20030303 ') AS 'NEW DATE'

In the above query, DATEADD function is used to add the number into the invalid format date value so the error message of cannot be converted to a valid DateTime value is display

OUTPUT:

 

Now we are going to take some examples of DATEADD with a database table for that we are considering student result management system database with two tables tblstudent,tblresult
Student Table

Result Table

 

Example-10: DATEADD SQL with table column name

we can specify SQL table column name as the input date to the DATEADD SQL function and perform date manipulation by adding or subtracting in the year, months, days, hours, or in minutes

Write SQL Query to add 10 days to the examdate of every semester

SELECT examname, DATEADD(DD, 10, examdate) AS 'NEW EXAM DATE'
FROM tblresult

• In the above query, DATEADD SQL is used to add 10 days extension to the examdate of every semester
• examdate column name is given as the input date and 10 is given as the number

OUTPUT:

 

Example-11: DATEADD SQL with where clause

We can use DATEADD SQL function to perform date manipulation based on conditions specified with the WHERE clause

Write SQL Query to extend semester 1 exam date for a month using DATEADD SQL function

SELECT examname, DATEADD(MM, 1, examdate) AS 'NEW EXAM DATE' FROM tblresult
WHERE (examname = 'sem1')

• In this query, DATEADD function is applied on column name examdate to extend the exam date by 1 month
• To perform extension based on the condition SQL Where clause is used

OUTPUT:

 

Summary

In this Article, we have covered an overview of the DATEADD SQL, the key point to remember for the function, Syntax with an explanation of each argument, Practical examples with adding days to the date, subtracting days from date, adding and subtracting year, months, hours and minutes, an example of adding quarter is also covered, at the article we have covered the example with table column name as input date and with the condition using SQL where clause

 

What's Next

SQL Date Format Explained with Examples
SQL Date Functions Explained with Practical Examples

 

Further Reading

SQL Dateadd() Function