CAML query not retrieving items based on Start date and end date for EST Time zone?

Question

I have one custom list name Test, Using the Following query

SPList PollList = web.Lists["Test"];
SPQuery PollQuery = new SPQuery();
var CurrentDate = DateTime.Today;

PollQuery.Query = "<OrderBy><FieldRef Name='StartDate' Ascending='TRUE'/></OrderBy><Where><And><Leq><FieldRef Name='StartDate'/><Value IncludeTimeValue='TRUE' Type='DateTime'>" + CurrentDate + "</Value></Leq><Geq><FieldRef Name='EndDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + CurrentDate + "</Value></Geq></And></where>";

the above querying retrieving the items for IST Time but it not retrieving the items for EST Time zone. Please Suggest me an idea to solve this issue

Answer 1

The correct date format to pass in your CAML query is yyyy-MM-ddTHH:mm:ssZ.

In case its javascript, you can convert date as below:

var inputDate = new Date();
string formattedDate = inputDate.ToString("yyyy-MM-ddTHH:mm:ssZ");

In case its is SSOM, you have 2 options

1) <Today /> operator. If you use this, your CAML would be as below:

PollQuery.Query = "<OrderBy><FieldRef Name='StartDate' Ascending='TRUE'/></OrderBy><Where><And><Leq><FieldRef Name='StartDate'/><Value IncludeTimeValue='TRUE' Type='DateTime'><Today /></Value></Leq><Geq><FieldRef Name='EndDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'><Today /></Value></Geq></And></Where>";

2) You also have the inbuilt SPUtility.CreateISO8601DateTimeFromSystemDateTime() method.For that you need to add the Microsoft.SharePoint.Utilities namespace for that. You CAML query would be as below:

PollQuery.Query = "<OrderBy><FieldRef Name='StartDate' Ascending='TRUE'/></OrderBy><Where><And><Leq><FieldRef Name='StartDate'/><Value IncludeTimeValue='TRUE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today) + "</Value></Leq><Geq><FieldRef Name='EndDate' /><Value IncludeTimeValue='TRUE' Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Today) + "</Value></Geq></And></Where>";