CAML query for datetime fields and current datetime in Sharepoint javascript object model: UTC, ISO8101, Today, Now, IncludeTimeValue and StorageTZ

Often we need to retrieve items from Sharepoint list using condition on datetime field comparing it with DateTime.Now including both date and time parts. In this post I will show several ways to achieve it using javascript object model.

Suppose that we need to query news which have PublishedDate field which is less or equal current datetime (item[“PublishedDate”] <= DateTime.Now). It is important to take into consideration time part in order to have correct listing for news published today (e.g. news which have PublishedDate set to 12:00 today should not be visible until 11:59, but should appear starting from 12:00).

In order to get such news we need to use the following CAML query:

   1: <Where>

   2:     <Leq>

   3:         <FieldRef Name="PublishedDate" />

   4:         <Value IncludeTimeValue="True" Type="DateTime">2017-01-04T12:00:00Z</Value>

   5:     </Leq>

   6: </Where>

Several notes about datetime value. First of all pay attention on IncludeTimeValue="True" attribute which tells Sharepoint to use time part for comparison. At second, value itself 2017-01-04T12:00:00Z is in ISO8601 format (in basic Sharepoint server object model there is convenient method SPUtility.CreateISO8601DateTimeFromSystemDateTime which formats datetime into this format). Note that it shouldn’t include milliseconds part .000 after seconds, i.e. you can’t use standard Date.toISOString() javascript function as is because of that (this function returns also milliseconds which breaks Sharepoint query engine). At third, you also can’t use Date.toISOString() javascript function because it converts datetime object to UTC format, while in regular CAML queries dates should not be in UTC (there is a way to use UTC datetime in CAML query, but you need to do additional action for that - add StorageTZ=”TRUE” attribute like shown e.g. here: Timezone offset comparisons in CAML with UTC parameter. In our example we don’t use this attribute, i.e. need to use datetime in local timezone, not in UTC). And at fourth, as we want to get news which have item[“PublishedDate”] <= DateTime.Now we of course should not hardcode value to the CAML query. Instead we need to get current datetime from server and format it to ISO8601 non-UTC format. In one of my previous posts I showed how to get current server’s datetime (see Get current server date time via javascript object model in Sharepoint):

   1: var currentServerDateTime = new Date(new Date().getTime() +

   2:     _spPageContextInfo.clientServerTimeDelta);

Here is full code which creates above CAML query using Sharepoint javascript object model:

   1: var currentServerDateTime = new Date(new Date().getTime() +

   2:     _spPageContextInfo.clientServerTimeDelta);

   3: var query = new CamlBuilder().Where().DateTimeField("PublishedDate")

   4:     .LessThanOrEqualTo(Utility.CreateISO8601DateTimeFromSystemDateTime(currentServerDateTime))

   5:         .ToString();

   6:  

   7: var camlQuery = new SP.CamlQuery();

   8: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");

   9:  

  10: var list = site.openWeb(webUrl).get_lists().getByTitle(listTitle);

  11: var items = list.getItems(camlQuery);

Here I used the following helper utility which works like SPUtility.CreateISO8601DateTimeFromSystemDateTime, but in javascript:

   1: var Utility = {

   2:     pad: function (number) {

   3:       if (number < 10) {

   4:         return '0' + number;

   5:       }

   6:       return number;

   7:     },

   8:  

   9:     CreateISO8601DateTimeFromSystemDateTime: function(dt) {

  10:       return dt.getFullYear() +

  11:         '-' + Utility.pad(dt.getMonth() + 1) +

  12:         '-' + Utility.pad(dt.getDate()) +

  13:         'T' + Utility.pad(dt.getHours()) +

  14:         ':' + Utility.pad(dt.getMinutes()) +

  15:         ':' + Utility.pad(dt.getSeconds()) +

  16:         'Z';

  17:     },

  18: };

Also for creating CAML query itself I used convenient utility CamlJS, which is similar to Camlex, i.e. allows to built CAML queries dynamically, but works in javascript.

Shown above method uses direct approach with comparing to exact server’s current datetime. However for achieving the same result we may use one non-well documented Sharepoint feature: use CAML query with <Today /> keyword and IncludeTimeValue="True" attribute. It will return the same items as above query, i.e. news which have item[“PublishedDate”] <= DateTime.Now using time part in comparision. You may think that instead of <Today /> and IncludeTimeValue="True" you may use another CAML keyword <Now />, but this is not the case – <Now /> should be used only inside DateRangesOverlap element – see the following forum thread for details CAML Now Instead of Today - CAML Bug?. Here is code which uses this approach:

   1: var currentServerDateTime = new Date(new Date().getTime() +

   2:     _spPageContextInfo.clientServerTimeDelta);

   3: var query = new CamlBuilder().Where().DateTimeField("PublishedDate")

   4:     .LessThanOrEqualTo(CamlBuilder.CamlValues.Today).ToString();

   5:  

   6: var camlQuery = new SP.CamlQuery();

   7: camlQuery.set_viewXml("<View><Query>" + query + "</Query></View>");

   8:  

   9: var list = site.openWeb(webUrl).get_lists().getByTitle(listTitle);

  10: var items = list.getItems(camlQuery);

Resulting CAML will be:

   1: <Where>

   2:     <Leq>

   3:         <FieldRef Name="PublishedDate" />

   4:         <Value IncludeTimeValue="True" Type="DateTime">

   5:             <Today />

   6:         </Value>

   7:     </Leq>

   8: </Where>

Hope that this information will help you.