cancel
Showing results for 
Search instead for 
Did you mean: 
Workflow Hero

Date Comparison in WF

Jump to solution

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.

Labels: (1)
Reply
12 Replies
Workflow Hero

Re: Date Comparison in WF

Jump to solution

Hi there,


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

Thanks

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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,

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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.

Reply
Not applicable

Re: Date Comparison in WF

Jump to solution

I agree, I am also experiencing the date match issue. I am trying to compare a list date column in a site workflow (query list) to pull list items where the above column is = Current date and I never get a match so I ended up with a variable of date type , set it to current date + 1 and then in the query list set the filter criteria as column date < datevariable.

With this I have to make it completely full proof by adding a new column to pull the desired rows.

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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.

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

Hi Rhia,

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

Thanks,

Mike

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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)

0 Kudos
Reply
Workflow Hero

Re: Date Comparison in WF

Jump to solution

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! 

0 Kudos
Reply