CAML & DateTime

May 5, 2008 at 7:26 PM (CAML, SharePoint)
Tags: CAML, SharePoint

Whenever querying anything with Date and Time as one of the field under <where\> condition

They should be converted to ‘ISO8601’ format i.e yyyy-mm-ddThh:mm:ss.. this can be done by calling
Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime

By default CAML only takes the date as parameter and ignores the Time. To have Time also as a condition it requires
IncludeTimeValue=’TRUE’as parameter
for e.g
<FieldRef Name=’Created’ /><Value Type=’DateTime’ IncludeTimeValue=’TRUE’/>

To have the current date use : <Today />

One of the most common question when using CAML is , “is it possible to do something like: get last 5 days data ? “

Or in another words would it be possible to introduce something like Today – 5 (days ?)
Big Answer after lots of search is YES
If you want to work with the date of today, you could use the syntax <Today /> and if you want to do simple calculations i.e. adding or subtracting a certain number of days of today’s date you could make use of the syntax <Today OffsetDays=”5″ />.

In a content query web part you could integrate a query like the following:
<WHERE>
<GE>
<FieldRef Name=”StartDate”/>
<Value Type=”DateTime”><Today OffsetDays=5 /></Value>
</GE>
</WHERE>
In case of more complex calculations on dates, you could make use of SPUtility.CreateISO8601DateTimeFromSystemDateTime in combination with the different calculation methods on the DateTime data type.

I have seen that some guys uses [Toda-2Day(s)] format to add/substract no if days from date, but it never worked for me.