Javascript CAML Query using DateTime

Question

I have the following CAML:

<Where>
    <And>
        <Eq>
            <FieldRef Name='Author' LookupId='TRUE' />
            <Value Type='User'>473</Value>
        </Eq>
        <Eq>
            <FieldRef Name='Datum' />
            <Value Type='DateTime'>2015-06-05T00:00:00Z</Value>
        </Eq>
    </And>
</Where>

In my list I have 8 items, 7 of these have Author equals 473 and 2 of these have Datum equals 2015-06-05. Whatever value I pass to the Datum i always get 7 items. What is wrong?

Answer 1

I wasn't able to find out the problem, but with the following workaround I got it:

<Where>
<And>
    <Eq>
        <FieldRef Name='Author' LookupId='TRUE' />
        <Value Type='User'>473</Value>
    </Eq>
    <And>
        <Leq>
            <FieldRef Name='Datum' />
            <Value Type='DateTime'>2015-06-05 00:00:00</Value>
        </Leq>
        <Geq>
            <FieldRef Name='Datum' />
            <Value Type='DateTime'>2015-06-05 00:00:00</Value>
        </Geq>
    </And>
</And>

Answer 2

Please check the time zone of your server. It is possible that your web time zone and browser time zone differs. You should try these options:

1. Set property SP.CamlQuery.datesInUtc to false
2. Set property SP.CamlQuery.datesInUtc to true and pass correct value in UTC. For example, if your web time zone is +2 UTC, browser shows date value 2015-06-05 00:00:00, but real value in SharePoint database is 2015-06-04 22:00:00

Play with this settings and values.

Answer 3

You can use LocalTime to UTC convertion. Example in pnp js:

const yourDateTime = new Date();
const timezone = sp.web.regionalSettings.timeZone;
const dateTimeForCaml = (await timezone.localTimeToUTC(yourDateTime)).toString();

Its solve my issue even when I have time zone differences with SharePoint.