Sharepoint list date format

Question

The date format for sharepoint lists is : M/D/YYYY I have to columns in the splist: start_time and end_time, and I have to write a query where I get those fields where "todays"date is greater or equal than start_time and less than or equal than end_time. Of course I also have hours:minutes in my columns. The problem I have is that I found out that CAML date format is: yyyy/mm/ddThrs:min:ssZ. I know about the tag to get todays date but how do I change that date format from the fields so the comparison in the query succeeds?

This is a portion of the code:

query.Append("<FieldRef Name = 'Start_Time'");
query.Append("/>");
query.Append("<Value Type ='DateTime'IncludeTimeValue='True'>");
query.Append("<Today/>");
query.Append("</Value>");

The comparison will fail since the splist date format is not in CAML date format

Rich Bennema Rich Bennema · Accepted Answer · 2012-02-13T15:50:32

Try:

<Where>
  <And>
     <Geq>
        <FieldRef Name='start_time' />
        <Value Type='DateTime'><Now /></Value>
     </Geq>
     <Leq>
        <FieldRef Name='end_time' />
        <Value Type='DateTime'><Now /></Value>
     </Leq>
  </And>
</Where>

As long start_time and end_time are actually of type Date and not Text, a query like this should work. Regardless of how the dates are displayed on the screen, dates will use the ISO8601 format in CAML.

Note, the above CAML was build with the help of the U2U CAML Query Builder.

Sharepoint list date format

2 Answers