cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Comparing date value to today's date does not work in Query List

Jump to solution

I have been trying quite a number of ways but still coming up with a 0. Will appreciate any help from Nintex gurus here.

Background:

I am trying to have a site workflow send out email notifications to staff based on a date field in a list.

Method 1: Use a variable, TodayDate, set it to blank and call up a Set variable action and set TodayDate = Current Date

However, in the Query List, comparing TodayDate with the date field brings up no results regardless of whether I use TodayDate more than, less than, equal to field date. The only one that gives output is TodayDate not equal to field date, which will return all rows.

Method 2: 

Using the TodayDate, I convert it into a serial number (TodayDate - "1/1/1900"). This value is in seconds so I convert it into days.

Add an additional calculated column in my list that gives the date field output in a number field

Compare TodayDate with the new calculated column.

Unfortunately, this is also not working regardless of the comparison operator. The most frustrating thing is that one of the field values is the same as TodayDate, and setting TodayDate = calculated serial date does not give that row result.

If I use "is not equal to", I will get all the rows (even the one which is the same value as TodayDate).

But if I use any other operator, I will not get any results.

Labels: (2)
Reply
14 Replies
Automation Master
Automation Master

Re: Comparing date value to today's date does not work in Query List

Jump to solution

I think here is solution for you  

0 Kudos
Reply
Not applicable

Re: Comparing date value to today's date does not work in Query List

Jump to solution

Hi Marian,

I don't think that is the solution for me. I have gone beyond configuring the date in ISO format - I have converted the date values to serials and they still do not respond to < or > operators. In fact, even not equal to does not respond correctly.

The above is the email notification. I put in the last 2 as a check. This one had the condition of not equal to. In this case, this particular entry should not have been processed at all since the 2 values are equal.

Cheers,

Chris

0 Kudos
Reply
Automation Master
Automation Master

Re: Comparing date value to today's date does not work in Query List

Jump to solution

what's data type of 'Text Alert Date'?

can you post CAML so that I can see whole WHERE?

0 Kudos
Reply
Not applicable

Re: Comparing date value to today's date does not work in Query List

Jump to solution

Hi Marian,

Text Alert Date (in the list) is a calculated field with the output in number.

The CAML is here:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Neq>
<FieldRef Name="Text_x0020_Alert_x0020_Date" />
<Value Type="Number">{WorkflowVariable:TodayDateConvert}</Value>
</Neq>
</Where>
</Query>

This returns all the fields, even though there is 1 which should not be returned. Using any other operator results in no data returned.

Cheers,

Chris

0 Kudos
Reply
Highlighted
Automation Master
Automation Master

Re: Comparing date value to today's date does not work in Query List

Jump to solution

so apparently TextAlertArea do not evaluate to the (exact) value you compare it to.

I would say this is matter of rounding/presentation. regardless of how you set up field to show whole part of number (rounded), internally result of calculation is still fractional number.

so your condition matches just in case when decimal part is exactly 0.

I' ve to setup similar test case like yours. see an example

'user friendly' numbers

'real' values

query result

btw, you following premise

Using the TodayDate, I convert it into a serial number (TodayDate - "1/1/1900"). This value is in seconds so I convert it into days.

is not correct, difference of two dates is number of days, not seconds.

I would suggest not to complicate things by custom conversions/calculation but rather follow CAML specification and use date in ISO format.

btw2, if your only date to compare to is TODAY, you can take advantage of  predefined CAML elements <Today /> and/or  <Now />

see Query Schema 

Reply
Not applicable

Re: Comparing date value to today's date does not work in Query List

Jump to solution

Hi Marian,

I have set aside my assumptions and changed the workflow. Here's my new workflow:

Basically, I have 3 variables (for testing purposes):

CompareDate (Date/Time variable)

CompareDateText (Single line of text)

CompareDateISO (Single line of text)

Step 1: Set CompareDate to -1 month of today's date and set CompareDateText to the ISO 8601 date string

Step 2: Set CompareDateISO to ISO-formatted CompareDate value

Step 3: Do a comparison of my DueDate field in my list to filter out the choices.

Here is the issue (again): If I input in the comparison an actual value like "2016-11-08T16:00:00Z", it actually works and I get 1, 2 or 3 records back (depending on the value).

If I compare it with CompareDate, CompareDateText and CompareDateISO, I can't get any results.

Here is the output from my 3 values:

I think the issue is that my DueDate needs to compare with a Date/Time variable in ISO format. Would it be able to format the variable in ISO format while still keeping the Date/time variable type?

Cheers,

Chris

0 Kudos
Reply
Automation Master
Automation Master

Re: Comparing date value to today's date does not work in Query List

Jump to solution

let's take it from the bottom

Would it be able to format the variable in ISO format while still keeping the Date/time variable type?

Date/time is a datatype with it's internal structure/interpretation of date/time value (eg. number of ticks since some epoch)

ISO formatted string is just textual human/machine readable interpretation of that date/time value.

so the answer is no, you can not have a variable with two datatypes (date and string)  ta the same time.

I think the issue is that my DueDate needs to compare with a Date/Time variable in ISO format.

definitely for CAML.

Here is the issue (again): If I input in the comparison an actual value like "2016-11-08T16:00:00Z", it actually works and I get 1, 2 or 3 records back (depending on the value).

 If I compare it with CompareDate, CompareDateText and CompareDateISO, I can't get any results.

if it works for scalar value I don't see a reason why it shouldn't work for correctly formatted variable.

what comparison operator have you used?

isn't it problem that you compare scalar value of 8th of Nov, whereas variable with value of 12th of Nov?

can you post your CAML and example of date from list?

0 Kudos
Reply
Not applicable

Re: Comparing date value to today's date does not work in Query List

Jump to solution

My date list:

CAML for static value:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Geq>
<FieldRef Name="Next_x0020_Alert_x0020_Date" />
<Value Type="DateTime">2016-11-12T16:00:00Z</Value>
</Geq>
</Where>
</Query>

CAML for CompareDateISO:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Geq>
<FieldRef Name="Next_x0020_Alert_x0020_Date" />
<Value Type="DateTime">{WorkflowVariable:CompareDateISO}</Value>
</Geq>
</Where>
</Query>

CAML for static value will retrieve item 1, 2 from the list.

Cheers,

Chris

0 Kudos
Reply
Not applicable

Re: Comparing date value to today's date does not work in Query List

Jump to solution

Oh I made a mistake with the CAML. It should be LEQ instead of GEQ.

Updated CAML query for static:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Leq>
<FieldRef Name="Next_x0020_Alert_x0020_Date" />
<Value Type="DateTime">2016-11-12T16:00:00Z</Value>
</Leq>
</Where>
</Query>

The results for this are flipped.

Updated CAML for CompareDateISO:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Leq>
<FieldRef Name="Next_x0020_Alert_x0020_Date" />
<Value Type="DateTime">{WorkflowVariable:CompareDateISO}</Value>
</Leq>
</Where>
</Query>

Incidentally, I also tried the following CAML:

<Query>
<Lists>
<List ID="{525456B4-D8B8-4E7B-A14B-2B73CF052F39}" />
</Lists>
<ViewFields>
<FieldRef Name="ID" />
</ViewFields>
<Where>
<Leq>
<FieldRef Name="Next_x0020_Alert_x0020_Date" />
<Value Type="DateTime"><Today Offset='-30' /></Value>
</Leq>
</Where>
</Query>

Which would theoratically work, but doesn't. The results will always be all 3 rows, regardless of what I put in the Offset variable. Conversely, if you use GEQ for the CAML, there would be no returns.

Cheers,

Chris

0 Kudos
Reply