Issue with CAML Query In Javascript RRS feed

  • Question

  • I've ran into a bit of a doozie on this one, so I figured my next stop as the TN forums...

    I've created a Web Part that queries an external list (so it pulls data from a SQL database). I've run into an issue where my query is pulling in a date that should appear on the set of changes from last week, but appears on this week, which I'm leave to believe is caused by a UTC conversion in the CAML query.

    The change listed for Jan 28th, should appear on last week, rather than this week. The data range for these queries is from midnight of the allotted week's Sunday, until the following midnight Sunday (so in this case, it would be Jan 29th - Feb 5th for this week).

    One thing to note, is that the SQL server holds local date/time data, rather than UTC fields, so I had to edit the Business Data Connection Model to update the fields being pulled in through a stored procedure (SharePoint Designer defaults to UTC, so the times were not correct). So everything at this point is correct as far as the date/time goes in the display/list data etc, however, this one date is not showing in the proper week. I'm lead to believe that the caml query is converting the date/time of this one to 1/29 4am (so +5 hours UTC) and it then falls in the query range.

    Here are some examples I have tried with my query:

    <Query><Where><And><Geq><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="FALSE">Sun, 29 Jan 2017 05:00:00 GMT</Value></Geq><Lt><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="FALSE">Sun, 05 Feb 2017 05:00:00 GMT</Value></Lt></And></Where><OrderBy><FieldRef Name="RequestDate" Ascending="True"/></OrderBy></Query>

    The above example attempts to convert my start/end date/time values into a UTC String and then plug it into the query. I've tried to include the time value, or set it to false. I've also tried to use StorageTZ="True"/"False" with no luck either.

    <Query><Where><And><Geq><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="FALSE">1/29/2017</Value></Geq><Lt><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="FALSE">2/5/2017</Value></Lt></And></Where><OrderBy><FieldRef Name="RequestDate" Ascending="True"/></OrderBy></Query>

    This query example attempts to convert the values to LocaleDateString, so that it only uses a date field. Looking at the data in the image below, you'll see it's listing 1/28 (which is correct when looking at the list data and item specifically), but somewhere it's converting it to UTC and causing it to fall into this range.

    Note the values are ones plugged in by a variable I've created to determine the start and end of the week selected.

    If anyone can provide some insight on how to correct this, it would be VERY helpful, as I've ran out of things to try. Is there a way to force a CAML query to use Local Time Zone when performing its queries?

    Monday, January 30, 2017 10:10 PM


  • After sheer determination, I believe I was able to find a fix for my query. Although, I cannot quite determine why this works (my brain might be fried from simply looking at this all day). It appears I had to turn my comparison values around a bit.

    <Query><Where><And><Gt><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="TRUE">' + startDate + '</Value></Gt>' + '<Leq><FieldRef Name="RequestDate" /><Value Type="DateTime" IncludeTimeValue="TRUE">' + endDate + '</Value></Leq></And></Where><OrderBy><FieldRef Name="RequestDate" Ascending="True"/></OrderBy></Query>

    Monday, January 30, 2017 11:15 PM