DATEADD alters a date by adding or subtracting units of time. It returns the altered date/time.

Syntax

DATEADD( <date-part>, <number>, <date> )

Details

• Date Part — Determines the type of unit and number to be added.
• Number — The number of units. This can be used to add or subtract years, months, hours, etc. To subtract, use a negative value.
• Date — The date to alter.

Date Parts

• Year — yy (or yyyy)
• Month — m (or mm)
• Hour — hh
• Minute — mi (or n)
• Quarter — q (or qq)
• Day of Year — dy (or y)
• Week — wk (or ww)
• Weekday — w (or dw)
• Day — d (or dd) For adding days, you may want to just add whole integers to a date. This has the same effect and is more efficient.

Examples

One year ago:

DATEADD(yy,-1,GETDATE())

This example uses weekday with a datediff to find the first of the week.

DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

Resources

• Dynamic Dates
• MSDN DATEADD (Transact-SQL)
• GETDATE
• DATEDIFF
• DATEPART
• DATENAME

View Examples

• Expiring Product Option Prices