DATEADD (Report Builder)

DATEADD (Report Builder)
This page is specific to: Version:SQL Server 2005

SQL Server 2005 Books Online (November 2008)

Updated: 12 December 2006

Returns a datetime that is the result of adding the specified number of time interval units to the original datetime.

Syntax

DATEADD(interval, units, datetime)

interval	Specifies the units (year, quarter, month, day, hour, minute, second or week) used to calculate the date addition.
units	Specifies the number of intervals.
datetime	Specifies the date to which the intervals are added.

Remarks

Intervals are not case-sensitive.

The following intervals are valid.

Interval	Result
YEAR	Returns a date that is the number of years specified from the datetime.
QUARTER	Returns a date that is the number of quarters specified from the datetime.
MONTH	Returns a date that is the number of months specified from the datetime.
WEEK	Returns a date that is the number of weeks specified from the datetime.
DAY	Returns a date that is the number of days specified from the datetime.
HOUR	Returns a date that is the number of hours specified from the datetime.
MINUTE	Returns a date that is the number of minutes specified from the datetime.
SECOND	Returns a date that is the number of seconds specified from the datetime.

Example

Formula	Result
DATEADD(MONTH, 3, #8/18/2009#)	11/18/2009
DATEADD(DAY, 365, Sell End Date)	Returns a sell end date for each instance that is 365 days from the original sell end date. For example, if the original sell end date is 12/18/2009, the result is 12/18/2010.

Concepts

Working with Formulas (Report Builder)
Using Functions (Report Builder)
Date and Time Functions (Report Builder)
Report Builder How-to Topics
Working with Literals in Formulas and Filters (Report Builder)
Formatting Numerical Data (Report Builder)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
12 December 2006	Updated content: Quotation marks are no longer required for intervals.
14 April 2006	New content: Added Remarks section.

Community Content

Incorrect Documentation

Added by:MrDerikart

I just tried the value DAY for the DateInterval object. It did not work. However, using "d" for the value did work. Please correct this documentation.

DATEADD (Report Builder) Mis-Information

Added by:Tigris

Thanks MrDerikart, I appreciate the insight... saved me some headaches. Can someone please correct the misinformation?

Re: Incorrect Documentation

Added by:Shiva -

Thanks. Looks like no-one from Microsoft is actually reviewing the "community content" :)

So for the rest of ya'll here are some examples of correct usage. A straight copy/paste of these examples should work.

To Subract a day from the current day (date only, no time)

=DATEADD("d",-1,TODAY())

To Subtract 24 hours from the current date (and time)

=DATEADD("d",-1,NOW)

To add 3 hours in the future to the current date (and time)

=DATEADD("h",3,NOW)

Essentially it seems to be using the .Net datetime format strings for the DateInterval Parameter (1st parameter).

All custom date and time format strings are listed here - http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

-Shiva
http://mycodetrip.com