DATEADD() Function in SQL Server
Last Updated :
18 Jan, 2021
DATEADD() function :
This function in SQL Server is used to sum up a time or a date interval to a specified date, then returns the modified date.
Features :
- This function is used to sum up a time or a date interval to a date specified.
- This function comes under Date Functions.
- This function accepts three parameters namely interval, number and date.
- This function can also include time in the interval section.
Syntax :
DATEADD(interval, number, date)
Parameter :
This method accepts three parameters as given below as follows.
- interval –
It is the specified time or date interval which is to be added. Moreover, the values of the interval can be as given below.
year, yyyy, yy = Year, which is the specified year to be added.
quarter, qq, q = Quarter, which is the specified quarter to be added.
month, mm, m = month, which is the specified month to be added.
dayofyear, dy, y = Day of the year, which is the specified day of the year to be added.
day, dd, d = Day, which is the specified day to be added.
week, ww, wk = Week, which is the specified week to be added.
weekday, dw, w = Weekday, which is the specified week day to be added.
hour, hh = hour, which is the specified hour to be added.
minute, mi, n = Minute, which is the specified minute to be added.
second, ss, s = Second, which is the specified second to be added.
millisecond, ms = Millisecond, which is the specified millisecond to be added.
- number –
It is the number of interval which is to be added to the date specified. It can be positive, in order to get the dates of the future or it can be negative also, in order to get the dates in the past.
- date –
It is the specified date which is to be altered.
Returns :
It returns a modified date after adding a date or time interval to the stated date.
Example-1 :
Using DATEADD() function and adding the year part of the date for getting the modified date.
SELECT DATEADD(year, 2, '2019/01/05');
Output :
2021-01-05 00:00:00.000
Example-2 :
Using DATEADD() function and adding the month part of the date for getting the modified date.
SELECT DATEADD(month, 11, '2019/01/05');
Output :
2019-12-05 00:00:00.000
Example-3 :
Using DATEADD() function and subtracting the month part of the date for getting the modified date.
SELECT DATEADD(month, -1, '2019/01/05');
Output :
2018-12-05 00:00:00.000
Example-4 :
Using DATEADD() function and adding the day part of the date for getting the modified date.
SELECT DATEADD(day, 32, '2015/04/14');
Output :
2015-05-16 00:00:00.000
Example-5 :
Using DATEADD() function and adding the minute part of the date for getting the modified date.
SELECT DATEADD(minute, 6, '2015/04/14 09:55');
Output :
2015-04-14 10:01:00.000
Example-6 :
Using DATEADD() function and adding the hour part of the date using a variable for getting the modified date.
DECLARE @number INT;
SET @number = 8;
SELECT
DATEADD(hh, @number, '2021/01/02 08:50');
Output :
2021-01-02 16:50:00.000
Example-7 :
Using DATEADD() function and adding the second part of the date using variables for getting the modified date.
DECLARE @number INT;
DECLARE @date VARCHAR(50);
SET @number = 08;
SET @date = '2011/11/22 07:59:56';
SELECT
DATEADD(ss, @number, @date);
Output :
2011-11-22 08:00:04.000
Application :
This function is used to find the modified date after adding a date or time interval to the stated date.
Similar Reads
DATENAME() Function in SQL Server
DATENAME() function : This function in SQL Server is used to find a given part of the specified date. Moreover, it returns the output value as a string. Features : This function is used to find a given part of the specified date.This function comes under Date Functions.This function accepts two para
2 min read
DATEPART() Function in SQL Server
DATEPART() function :This function in SQL Server is used to find a given part of the specified date. Moreover, it returns the output value as an integer.Features : This function is used to find a given part of the specified date.This function comes under Date Functions.This function accepts two para
2 min read
DAY() Function in SQL Server
DAY() function : This function in SQL Server is used to return the day of the month i.e, from 1st to 31st for date stated. Features : This function is used to find the day of the month for a date specified. This function comes under Date Functions. This function accepts only one parameter i.e, date.
2 min read
ATN2() Function in SQL Server
In this article, we are going to cover the ATN2()function in which we will see how we can get the arc tangent of two given numbers. Let's discuss one by one. // here val1 and val2 are input. Input : ATN2(val1, val2) Output : Arc tangent result. ATN2() : It is the function that returns the arc tangen
1 min read
SQL Server DATEDIFF() Function
The DATEDIFF() function in SQL Server is a powerful tool used to calculate the difference between two dates or times. It returns an integer representing the number of date or time boundaries crossed between the specified dates, based on the specified date. This function is essential for tasks that i
4 min read
DATEFROMPARTS() Function in SQL Server
DATEFROMPARTS() function : This function in SQL Server is used to return a date from the given values of year, month and day. Features : This function is used to find a date from the stated values of year, month and day. This function comes under Date Functions. This function accepts three parameter
2 min read
COUNT() Function in SQL Server
The COUNT() function in SQL Server is a fundamental aggregate function used to determine the number of rows that match a specific condition. Counting rows provides valuable insights into data sets such as the total number of records, distinct values, or records meeting certain criteria. In this arti
3 min read
SQL Server FORMAT() Function
SQL Server FORMAT() function formats the specified value in the given format. FORMAT Function in SQL ServerThe FORMAT function in SQL Server is used to format date/time and number values with locale-aware formatting. The FORMAT function achieves various formatting requirements, showing dates in spe
2 min read
LOG() Function in SQL Server
The LOG() function returns the logarithm of a specified number or the logarithm of the number to the specified base. Syntax : LOG(number, base) Parameter : LOG() function accepts two-parameters as mentioned above and described below. number - This parameter hold a number which is greater than 0. bas
1 min read
TAN() and COT() Function in SQL Server
1. TAN() Function : The TAN() function returns the tangent of a number. Syntax : TAN(number) Parameter : Required. A numeric value. number : It is a numeric value. Returns : It returns the float_expression of the tangent of a number. Example-1 : When the arguments holds a positive number. SELECT TAN
2 min read