Comparison of Date and Time types in CAML

CAML is often used for list query of Sharepoint instances. Today, I am querying a workflow authorization list, the authorizer must be found based on the personnel, workflow name, authorization start time, and authorization end time. If the authorizer is not found, the user is returned. Use CAML QueryBuilder to generate a query, and then modify the corresponding variables to perform the query. The query results are incorrect. Originally, the representation of the date type used in CAML must be a special format rarely used in China, such as "2009-099t15: 11: 20Z" online, use SPUtility. createISO8601DateTimeFromSystemDateTime

This function can generate this format. Of course, it is actually possible to have Datetime. ToString ("format. The queried CAML is as follows:

String dString = SPUtility. CreateISO8601DateTimeFromSystemDateTime (DateTime. Now );
Q. Query =
@ "<Where>
<And>
<And>
<And>
<Contains>
<FieldRef Name = '_ x5de5 _ x4f5c _ x6d41 _'/>
<Value Type = 'lookupmulti'> "+ wfName + @" </Value>
</Contains>
<Eq>
<FieldRef Name = 'author' LookupId = 'true'/>
<Value Type = 'user'> "+ us. ID + @" </Value>
</Eq>
</And>
<Lt>
<FieldRef Name = '_ x5f00 _ x59cb _ x65f6 _ x95f4 _'/>
<Value Type = 'datetime'> "+ dString + @" </Value>
</Lt>
</And>
<Gt>
<FieldRef Name = '_ x7ed3 _ x675f _ x65f6 _ x95f4 _'/>
<Value Type = 'datetime'> "+ dString + @" </Value>
</Gt>
</And>
</Where> ";

In most cases, the query is correct, but sometimes it is still wrong, that is, on the day. For example, I set the start time to 10:00:00, and the current time is on the 9 th, but no results are returned for the query. After many experiments, I finally found the cause, in the past, we did not compare the time field at all, but compared the date. Because the date in April 9 is not later than in April 9, we cannot find the result.

To compare the time fields, you must add IncludeTimeValue = 'true' to the Value field in CAML. Then, you can compare the time fields.

String dString = SPUtility. CreateISO8601DateTimeFromSystemDateTime (DateTime. Now );
Q. Query =
@ "<Where>
<And>
<And>
<And>
<Contains>
<FieldRef Name = '_ x5de5 _ x4f5c _ x6d41 _'/>
<Value Type = 'lookupmulti'> "+ wfName + @" </Value>
</Contains>
<Eq>
<FieldRef Name = 'author' LookupId = 'true'/>
<Value Type = 'user'> "+ us. ID + @" </Value>
</Eq>
</And>
<Lt>
<FieldRef Name = '_ x5f00 _ x59cb _ x65f6 _ x95f4 _'/>
<Value Type = 'datetime' IncludeTimeValue = 'true'> "+ dString + @" </Value>
</Lt>
</And>
<Gt>
<FieldRef Name = '_ x7ed3 _ x675f _ x65f6 _ x95f4 _'/>
<Value Type = 'datetime' IncludeTimeValue = 'true'> "+ dString + @" </Value>
</Gt>
</And>
</Where> ";

Here I made a mistake when writing, and wrote IncludeTimeValue = 'true' to FieldRef. I thought it was the same as LookupId = 'true' and the result was always not compared with the time field, attention should also be paid here.

In addition, a CAML syntax is attached:

CAML syntax-Query writing

Element description
And
BeginsWith starts with a string
Contains a string
Equal to Eq
FieldRef references a field (used in GroupBy)
Geq greater than or equal
Group by group
Gt>
IsNotNull is not empty
IsNull null
Leq less than or equal
Lt is less
Neq is not equal
Now current time
Or
OrderBy sorting
Today's date
TodayIso today's date (ISO format)
Where clause