When filter data from a SharePoint List on Modified, has anyone had a problem where time is not being compared?

S
Badge +9

I've just experienced a problem comparing two dates and times on a SharePoint List, one of them is the built-in Modified column. The other column is one I built with Nintex that I ensured had a time component.  I know the Modified in SharePoint has a time component because you can see such listings as "1 minute ago", "two hours ago".

When I go to compare these in a Query List Item, the Item only returns the result I feel should be returned if the Date of the column I specified is before today.  If I specify a date several hours less than the Modified Date, the query will not return any items--even though I know they are present. It ONLY seems to be doing a DAY compare.

I would like to only update if something changes. I don't want to resort to flags and updating flags.

Anyone seen this type of behavior?

198565_pastedImage_1.png

10 replies

J
Badge +6

Hey Stephan Onisick‌,

could you maybe store both values in a string variable that has the ISO 8601 format and then try it again? With this approach you could make sure that both values definitely have the same type/format.

198655_pastedImage_1.png

Best regards,

Jan

S
Badge +7

I had a similar issue using the query list,filter control and dates. When running the Execute now option I was getting no results. However it was working when running as normal.

E
Userlevel 5
Badge +14

the reason is how nintex builds CAML query behind. if you switched to CAML editor you would see something like

198741_pastedImage_5.png

CAML specification says that until IncludeTimeValue  clause of Value tag  is specified, time portion is ignored

see specification here https://msdn.microsoft.com/en-us/library/office/ms441886.aspx 

the other point is, as suggested by Jan von Reith‌, that your datetime has to be provided in ISO format (ie. you do not need to convert list field's value as Jan suggests)

see following blog regarding date in CAML/Sharepoint SharePoint 2010 Working with Dates in CAML - CodeProject 

here you can see some real examples

Nested "For each" 

Filtering multiple fields for Null Values 

S
Badge +9

Would you just stick it into the XML like this:

<Query>
<Lists>
<List ID="{E7A31207-C9EF-4F0A-BF2D-3BBDC0B024B8}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<And>
<Eq>
<FieldRef Name="ActionItemKey" />
<Value Type="Number">{WorkflowVariable:ActionItemId}</Value>
</Eq>
<Gt>
<FieldRef Name="Modified" />
<Value Type="DateTime" IncludeTimeValue = "TRUE">{WorkflowVariable:LoopDetailUpdateDate}</Value>
</Gt>
</And>
</Where>
<OrderBy>

E
Userlevel 5
Badge +14

I assume LoopDetailUpdateDate is datetime variable...

then that's just half of the work.

you still have to convert value of LoopDetailUpdateDate to its ISO formatted representation.

S
Badge +9

I'm assuming I can get the variable and then use the Calculate Date widget and specify iso fomat.

Thanks so much for enlightening me. I just though it didn't work.

S
Badge +9

Does this make sense. I'm comparing a Date/Time to a String

<Query>
<Lists>
<List ID="{E7A31207-C9EF-4F0A-BF2D-3BBDC0B024B8}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<And>
<Eq>
<FieldRef Name="ActionItemKey" />
<Value Type="Number">{WorkflowVariable:ActionItemId}</Value>
</Eq>
<Gt>
<FieldRef Name="Modified" />
<Value Type="DateTime" IncludeTimeValue="true">{WorkflowVariable:LoopDetailUpdateISOString}</Value>
</Gt>
</And>
</Where>
<OrderBy>
<FieldRef Name="StatusDate

S
Badge +9

My experience was the last formatting didn't matter.

E
Userlevel 5
Badge +14

it could work, if content of variables in in correct format.

you should be able to check its functionality with 'Run now' within Query list action, just replace all variable references with expected values.

E
Userlevel 5
Badge +14

sorry, do not get what you mean with this...

date formatting should not matter?

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings