Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

DATEADD Function in SharePoint Search SQL Syntax

Last modified: April 21, 2010

Applies to: SharePoint Server 2010

Performs time and date calculations for matching properties having date types.

DATEADD (DateTimeUnits, OffsetValue, DateTime)

Use the DATEADD function to obtain dates and times in a specified amount of time before the present.

DateTimeUnits specifies the units of the DateTimeValue parameter. This can be one of "YEAR", "QUARTER", "MONTH", "WEEK", "DAY", "HOUR", "MINUTE", or "SECOND". This value is case-sensitive. Quotation marks are not required around the parameter.

OffsetValue specifies the time offset, in the units specified by the DateTimeUnits parameter. OffsetValue must be a negative integer. Positive values are not supported.

DateTime is a time stamp from which to calculate the offset. This cannot be a date literal. It must be either GETGMTDATE or the result of another DATEADD function.

The DATEADD function can be used only in literal value comparisons and only on the right side of the comparison operator.

The GETGMTDATE function returns the current date and time in Greenwich Mean Time (GMT). Remember that this value might not be the same as the local time of your computer.

You can use multiple DATEADD functions to combine offset units.

Note Note

Do not use the equals (=) comparison operator because the internal time representation can produce rounding errors that result in unexpected matching results.

The following sample WHERE clause matches documents that were modified within the last five days:

...WHERE LastModifiedTime <=DATEADD (DAY, -5, GETGMTDATE())

The following sample WHERE clause matches documents that were modified within the last two days and four hours:

...WHERE LastModifiedTime <=DATEADD (DAY, -2, DATEADD (HOUR, -4, GETGMTDATE()))
© 2015 Microsoft