SQL Server DATEADD() Function

The SQL Server (Transact-SQL) DATEADD() function adds a number (a signed integer) to a datepart of an input date, and returns a modified date/time value.

For example, to find the date that is 1000 minutes from current time, the following arguments can be passed to this function:

number = 1000
datepart = minute
date = CURRENT_TIMESTAMP

Syntax

DATEADD(datepart, number, date)

Parameters

datepart

Required. Specify the unit type of interval to add. It can be one of the following values:

datepart	Abbreviations
year	yy, yyyy
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

number Required. An integer specifying the value of unit to add. Both positive and negative values are allowed.

date Required. Specify a date value to be modified.

Return Value

Returns the modified date/time value.

Example 1:

The example below shows the usage of DATEADD() function.

SELECT DATEADD(year, 5, '2019-10-25');
Result: '2024-10-25 00:00:00'

SELECT DATEADD(yyyy, 5, '2019-10-25');
Result: '2024-10-25 00:00:00'

SELECT DATEADD(yy, 5, '2019-10-25');
Result: '2024-10-25 00:00:00'

SELECT DATEADD(quarter, 2, '2019-10-25');
Result: '2020-04-25 00:00:00'

SELECT DATEADD(month, -2, '2019-10-25');
Result: '2019-08-25 00:00:00'

SELECT DATEADD(week, 1, '2019-10-25 08:10:25');
Result: '2019-11-01 08:10:25'

SELECT DATEADD(day, 10, '2019-10-25');
Result: '2019-11-04 00:00:00'

SELECT DATEADD(hh, 2, '2019-10-25 08:10:25');
Result: '2019-10-25 10:10:25'

SELECT DATEADD(hh, -2, '2019-10-25 08:10:25');
Result: '2019-10-25 06:10:25'

SELECT DATEADD(mi, 30, '2019-10-25 08:10:25');
Result: '2019-10-25 08:40:25'

SELECT DATEADD(ss, -10, '2019-10-25 08:10:25');
Result: '2019-10-25 08:10:15'

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpID	Name	Login Stamp	Expected Logout Stamp
1	John	2019-10-25 09:20:38	2019-10-25 17:50:38
2	Marry	2019-10-25 09:21:05	2019-10-25 17:51:05
3	Jo	2019-10-25 09:24:35	2019-10-25 17:54:35
4	Kim	2019-10-25 09:25:24	2019-10-25 17:55:24
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:57:16

To insert a new record in this table, the following statement can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', CURRENT_TIMESTAMP, DATEADD(mi, 510, CURRENT_TIMESTAMP));

-- see the result
SELECT * FROM EmployeeLogin;

This will produce a result similar to:

EmpID	Name	Login Stamp	Expected Logout Stamp
1	John	2019-10-25 09:20:38	2019-10-25 17:50:38
2	Marry	2019-10-25 09:21:05	2019-10-25 17:51:05
3	Jo	2019-10-25 09:24:35	2019-10-25 17:54:35
4	Kim	2019-10-25 09:25:24	2019-10-25 17:55:24
5	Ramesh	2019-10-25 09:27:16	2019-10-25 17:57:16
6	Suresh	2019-10-25 09:28:19	2019-10-25 17:58:19

❮ SQL Server Functions