T-SQL Tutorial T-SQL Advanced Database Management T-SQL References

T-SQL DATEADD() Function



The T-SQL (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:

datepartAbbreviations
yearyy, yyyy
quarterqq, q
monthmm, m
dayofyeardy, y
daydd, d
weekwk, ww
weekdaydw, w
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-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:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16
6Suresh2019-10-25 09:28:192019-10-25 17:58:19

❮ T-SQL Functions