SQL DATEADD Function

DATEADD adds a number of dateparts to a specified date.

Dateparts include year, month, day, minute, and others (values listed below).

The number of dateparts can be positive or negative.

Example

#

This example adds 1 year to a date.

SELECT DATEADD(year, 1, '2021-09-25') AS 'Year Later'
Result:  1 record
Year Later
2022-09-25 00:00:00.000

Usage

#

A common use is to add or subtract time from the current date.
In this example, 2 weeks are substracted.

SELECT DATEADD(week, -2, GETDATE()) AS '2 Weeks Ago'
Result:  1 record
2 Weeks Ago
11/23/2021 4:34:51 PM

Syntax

Syntax of the DATEADD function .

DATEADD(date_part, value, input_date) 

date_part -- the part of date to which the value will be added (values listed below).

value -- a signed integer number that is added to the date_part of the input_date.

input_date -- the original datetime, date, or time value.

These are valid date_part values with possible abbreviations

Date part Abbreviation
year yyyy or yy
quarter qq or q
month mm or m
dayofyear dy or y
day dd or d
week wk or ww
weekday dw
hour hh
minute mi or n
second ss or s
millisecond ms
microsecond mcs
nanosecond ns

More Examples

DATEADD with day.

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List the expected shipping dates for all orders.
 SELECT FirstName, LastName, OrderNumber, 
        DATEADD(day, 3, OrderDate) AS 'Shipping Date'
   FROM [Order] O
   JOIN Customer C ON O.CustomerId = C.Id
Result:  830 records
FirstName LastName OrderNumber Shipping Date
Paul Perrier 542378 2012-07-07 00:00:00.000
Karin Josephs 542379 2012-07-08 00:00:00.000
Mario Pontes 542380 2012-07-11 00:00:00.000
Mary Saveley 542381 2012-07-11 00:00:00.000
Pascale Cartrain 542382 2012-07-12 00:00:00.000

DATEADD with month.

CUSTOMER
Id
FirstName
LastName
City
Country
Phone
ORDER
Id
OrderDate
OrderNumber
CustomerId
TotalAmount
Problem: List all orders with the last date that a customer can still return an item.
 SELECT FirstName, LastName, OrderNumber, 
        DATEADD(month, 1, OrderDate) AS 'Last Return Date'
   FROM [Order] O
   JOIN Customer C ON C.Id = O.CustomerId
Result:  830 records
FirstName LastName OrderNumber Last Return Date
Paul Perrier 542378 2012-08-04 00:00:00.000
Karin Josephs 542379 2012-08-05 00:00:00.000
Mario Pontes 542380 2012-08-08 00:00:00.000
Mary Saveley 542381 2012-08-08 00:00:00.000
Pascale Cartrain 542382 2012-08-09 00:00:00.000

You may also like



Guides


vsn 3.1