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,

Advertisement
  • 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

Datepart abbreviation
Year YYYY,YY
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

 

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.

Advertisement
  • 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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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

Advertisement

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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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

Advertisement

OUTPUT:

DATEADD SQL Function Tutorial [Practical Examples]

 

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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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

Advertisement
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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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

Advertisement
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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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

student_id studentname admissionno admissiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 reema@gmail.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 meena@gmail.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carli@gmail.com vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 dhiru@gmail.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 hiren@gmail.com surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 mahi@gmail.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com vadodara 1

Result Table

result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail

 

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

Advertisement
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:

DATEADD SQL Function Tutorial [Practical Examples]

 

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:
DATEADD SQL Function Tutorial [Practical Examples]

 

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

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X