CAML Query - DateTime

Question

Hello!
I wish to take data from the list, satisfying to the certain time range (BeginDate.....DateTime.....EndDate). How it can be made?

Answer 1

 

This CAML Query below return the items based on MyDateColumn with less than 5 days. Basically using a Greator or Equal operatior and providing Today date - 5 days as a filter.

 

<Query>

    <Where>

        <Geq>

            <FieldRef Name="MyDateColumn" />

            <Value Type="DateTime">

                <Today OffsetDays="-5" />

            </Value>

        </Geq>

    </Where>

</Query>

 

 

You can use greator or equal like above and less or equal filter with AND operator in CAML above to work with date range. 

 

If the date value is coming from your variable etc then you need to use SPUtility function and get the string required by CAML for date time variable.

 

   DateTime CustomDate = DateTime.Now;
            string strCustomDate  = SPUtility.CreateISO8601DateTimeFromSystemDateTime(CustomDate); 
 

 

Answer 2

Hi,
I have a question.
Can caml works with hours and minutes too ?
Because my doesn't :-(

I built this query

DateTime start = Convert.ToDateTime(_properties.AfterProperties["start"].ToString());

DateTime end = Convert.ToDateTime(_properties.AfterProperties["end"].ToString());

SPQuery query1 = new SPQuery();

query1.Query = "<Where>"+

"<And>" +

"<Eq>" + //Equal to

"<FieldRef Name='reason'></FieldRef>" +

"<Value Type='Text'>" + whatIsbooking + "</Value>" +

"</Eq>" +

"<Or>" +

"<And>" +

"<Geq>" + // Greater than or euqual to

"<FieldRef Name='end'></FieldRef>" +

"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(end) + "</Value>" +

"</Geq>" +

"<Leq>" + //less then or equal to

"<FieldRef Name='start'></FieldRef>" +

"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(end) + "</Value>" +

"</Leq>" +

"</And>" +

"<And>" +

"<Leq>" +

"<FieldRef Name='start'></FieldRef>" +

"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(start) + "</Value>" +

"</Leq>" +

"<Geq>" +

"<FieldRef Name='end'></FieldRef>" +

"<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(start) + "</Value>" +

"</Geq>" +

"</And>" +

"</Or>" +

"</And>"+

"</Where>";

It checks just days, not hours or minutes.

Answer 3

Hi ,

To include time in DateTime Query, you have to set IncludeTimeValue=”TRUE”.

 

Ex: <Value Type='DateTime' IncludeTimeValue='True'></Value>

Hope this helps :) .

Answer 4

buddy , check this out whole syntax including how to get the right format of datetime for CAML query http://muzms.blogspot.com/2010/01/datetime-value-comparison-in-caml-query.html

Answer 5

This was very useful information - I was banging my head on this all day. The SPUtility method formatted the string correctly, but I needed to format the string without using SharePoint utilities. When I looked through the standard formats for the ToString method, I found this option:

string camlQueryString = DateTime yourSampleDate.ToString("s");

When I executed the query on SharePoint, it also worked just fine!

Answer 6

Hello

The solution is already proposed by native CAML Query and  confirmed by "Tester"

I normally use something like that :

 <Query>
    <Where>
         <Leq>
        <FieldRef Name='BeginDate' />
        <Value Type='DateTime'  IncludeTimeValue='TRUE'><Today /></Value>
        </Leq>
    <Where>
</Query>

Same will apply to  EndDate, etc

Hope it helps Cheers :)

 

---

Momo

Answer 7

This link should help.

http://priteshdave.wordpress.com/2010/09/21/datetime-in-operator-using-caml/

Answer 8

Hello,

<OrderBy><FieldRef Name='CreateDate' Ascending='False' /></OrderBy>

I am not able to apply Order by on Date Column using CAML Query. Can anyone help how to order by in date column?

Thanks in advance

Answer 9

<OrderBy><FieldRef Name='CreateDate' Type='DateTime' Ascending='False' /></OrderBy>

Add Type='DateTime' attribute.