DATEADD

 

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

View Examples