Date Comparison in WF


Badge +5

I've run into some trouble trying to compare date fields.

 

I have a date value - dateNow which I calculate using the Calcuate Date control while using the "use date when action is executed" and "include time".

I then compare it to a From and a To value to see if a specific schedule applies. (these are Date & Time columns)

 

In my Query List action I check that the From <= dateNow <= TO.

 

The WF logs this:

 

FROM: 2015-03-06 9:00 AM

dateNow: 2015-03-06 3:48 PM

TO: 2015-03-06 3:00 PM

 

This means that dateNow is clearly outside of the interval (greater than TO) yet I still get this selection as part of my results.

Basically it seems as if the "visible" values didn't match the values when doing the comparison.

 

I ran into a similar issue in Visual Studio where the actual date value provided by SP was different than the display one. There I could resolve it with a Convert.ToDateTime and then a comparison to UtcNow. No idea how I'd go about doing that in the NWF.


11 replies

Userlevel 5
Badge +12

Hi there,


Can you post a screen shot of your Query List configuration? - mainly where you are doing the filter.

Thanks

Badge +5

Hi Mike,

First an image of the dateNow field where I'm setting it. (I've also tried this with the item field "Created"). The Person lookup works fine, it's just the date ones that are causing the problem.

2015-03-09 08_04_19-Workflow Designer.png

Now the filter:

2015-03-09 08_01_35-Workflow Designer.png

And the CAML:

<Query>

  <Lists>

    <List ID="{}" />

  </Lists>

  <ViewFields>

    <FieldRef Name="Safety_x0020_Group" />

    <FieldRef Name="ID" />

    <FieldRef Name="From" />

    <FieldRef Name="To" />

  </ViewFields>

  <Where>

    <And>

      <And>

        <Eq>

          <FieldRef Name="Person" LookupId="TRUE" />

          <Value Type="User">{WorkflowVariable:intPerson}</Value>

        </Eq>

        <Geq>

          <FieldRef Name="To"  />

          <Value Type="DateTime">{WorkflowVariable:dateNow}</Value>

        </Geq>

      </And>

      <Leq>

        <FieldRef Name="From"  />

        <Value Type="DateTime">{WorkflowVariable:dateNow}</Value>

      </Leq>

    </And>

  </Where>

</Query>

Regards,

Badge +5

Just want to close this topic as it's been answered by Pavel Svetleachni.

The date comparison in the query only uses the date portion - specifically "The time is used to calculate if one period of time turns into next day or not.  Thus only calculated based on actual day and hours are used if it is next day or not.  So, filtering by specific hour/min/second is not possible, it is only used to determine if next day is
added or subtracted based on calculation of date.
"

Knowing this functionality should help in designing your workflows.

Thanks Pavel.

Badge

This topic should not be closed, as there is definitely a problem with date matching.

I have found a workaround, which is, to convert both dates to strings and then use the fn-Substring function, to get both strings to an identical length.  Then the "date matching" comparison works.

Userlevel 6
Badge +15

I've marked an answer as correct for the time being, but this is not closed by any means - definitely need to look into this. I'm currently trying to compare 2 dates in a form, and despite being the same (and I've done a ConvertToDate on both), they seem to think they are not the same at all.. very strange.

Userlevel 5
Badge +12

Hi Rhia,

What are you using to compare the two dates?  Can you display the formula?

Thanks,

Mike

Userlevel 6
Badge +15

I figured out a way around it -- but I'm still surprised I can't just say date1 == date2 and get a true or false. (Same date format controls, same date entered - Nintex on prem 2013)

Badge +4

Hi Everyone,

I'm also facing issue with comparing two dates to get desired values.

Where the case is like, I have two dates CreatedDate and CalDate (today'sDate-30) and I'm querying a list with condition (when CreatedDate=CalDate get all ID collection).

But everytime I'm getting empty value.(no ID's are coming)

Please help me in this regard.

Thanks! 

Badge +3

I am also having this problem. Has anyone found a work around for this to ignore the time portion of the date?

Userlevel 5
Badge +14

1. ‌ and ‌, please create new questions for new problems!

2. search the forum for phrase like query list by date in ISO format.

here are some useful posts - https://community.nintex.com/search.jspa?q=query+iso&author=%2Fpeople%2F8567 

if that doesn't help, proceed acc. point 1.

Badge +5

Rhia,

What was your work-around?

Ken

Reply