Caml query to fetch data from Sharepoint list for a specific Date

camlsharepoint

I have a sharepoint list with one of hte columns being Created Date of Type DateTime and value allowed is OnlyDate.

The submitted date in my column is 05/18/2011. When I perform a search using the below CAML query, it does not give any results though I have two items with this date.

<Where><Eq><FieldRef Name='Date_x0020_Created' /><Value Type='DateTime'>5/18/2011</Value></Eq></Where>

I dont understand what is wrong with this query. Someone please help.

Best Answer

Sharepoint accepts datetime in ISO 8601 Datetime form in UTC. The format is

yyyy-MM-ddTHH:mm:ssZ

So for your example you can use

2011-05-18T00:00:00Z

SPUtility does provide a method to do this conversion. For creating this in Java you can use the following method:

private String changeDateToISO8601(Date date) {
    TimeZone timeZone = TimeZone.getTimeZone("UTC");
    DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
    dateFormat.setTimeZone(timeZone);
    String convertedDate = dateFormat.format(date);
    return convertedDate;
}

P.S. You may not be able to find both the items using this just by <Eq> tag as there may be time difference. For this to work you need to enclose the dates in <Leq> and <Geq> tags.

Related Topic