What is the best way of comparing two dates in a workflow?

  • 27 January 2017
  • 9 replies
  • 46 views

Badge +5

What is the best way of comparing two dates in a workflow?  I have the following use case:

I have two date/time variables, Ship1 and Ship2 and then I have another data/time variable that I will use to write to SQL.  Call that SQLShipDate.  My logic is as follows:

Set SQLShipDate = Ship1.

Set Ship2 from the list data.  If the list data is blank, the value that gets placed in here is 1/1/0001 12:00:00AM

I have a RUN-IF action that says, If Ship2 does not equal 1/1/0001 then SET SQLShipDate = Ship2

So on my TESTS here is what I get:

Ship1 = 1/26/2017

Ship2 = blank (1/1/0001)

SQLShipDate is 1/26/2017 (what it should be)

Ship1 = 1/26/2017

Ship2 = 3/1/2017

SQLShipDate is 1/26/2017 (this has failed the test.  It should be 3/1/2017)

As I run my workflows, the RUN-IF action never works.  No matter what date Ship2 is, it never passes the RUN-IF comparison.

I can't figure out what else to do.  Does anyone have any suggestions on how to do date comparisons or check a date variable for an empty value?  I also tried creating an empty date variable and comparing but that didn't work either.


9 replies

Userlevel 3
Badge +9

What are the workflow actions you are using to handle the logic of setting Ship2 varialbe, and how are you setting the value for the Ship2 variable?

Userlevel 3
Badge +9

Rather than setting a Ship2 date/time variable with a bogus date, I used an inline function to check the length of the field, and then used that to do my logic to determine of how to set the SQLShipDate.  I used a Set a Condition to check if the value of st_LengthShip2 is 0.  If it is, then I set the SQLShipDate = Ship1.  If not, then I set it equal to Ship2.  It worked.  

Build String

Userlevel 5
Badge +12

Hello,

I think what is happening to you is that the "time" portion of the date/time is being compared, therefore your action will never "equal" what's specified in it since the time is constantly changing.   I've seen this happen behind the scenes before and "time" is usually the culprit when date comparisons fail.    Are you able to do a "contains" instead of an equal?

Hope this helps,

Mike

Badge +3

I have a site workflow that executes the Query List action once a day at 6:00 am.  It uses the Date column of a list to determine if an email should be sent.  I created a variable "Todays Date" that is set to Today's Date.  

The Filter in my Query List is as follows:

Show the items when column

Date

is not null

And

When column:

Date

is greater than or equal to

Todays Date

The result is placed in a Date Collection and is then used in a For Each action to generate my email.

Hope this helps with your situation.

Userlevel 6
Badge +13

I've just updated your question title to reflect the post. Please try to keep the titles informative for other users on the forum.

Thanks

Badge +5

After lots of trial and error, I have abandoned Nintex workflow to accomplish this comparison.  I know that it isn't an answer to this problem, but without any specific date comparison functionality, I can't guarantee a result.  Some of my observations during my testing:

My original workflow parsed an XML date field and placed that date in a DateTime workflow variable.  What I found is that if there wasn't a date in the XML, the value that the workflow variable was set to was 1/1/0001 12:00AM.  I assumed that this was an empty variable so I first checked to see if the workflow variable was greater-than or equal to nothing.  That didn't work.

My second attempt was to create a new workflow variable that I called EmptyDate.  I set the default to nothing and then I used that in my comparison and that also failed.

My third attempt was to set the EmptyDate variable to 1/1/0001 12:00AM.  I then used this as a comparison and that also failed. Even when I had a valid date of 1/29/2017 12:00AM, it didn't work.  

I turned on verbose logging which brought my environment to a crawl.  I then had workflows timing out so I had to turn it back off.  I suppose that is another issue that I need to deal with.

As I said, I ended up solving the problem outside of my workflow.  I no longer need to compare dates.  

I'll let the administrator decide if this question should be closed.  I couldn't get any solutions to work.

Userlevel 5
Badge +14

try to compare dates through one of fn-DateDiffXXX functions

eg. fn-Abs(fn-DateDiffDays(ship1,ship2)) < 1

or convert dates to ISO strings and compare those

Badge +1

Comparing Dates in SharePoint

 

In response to your situation above, here are a few tips.

Tip 1:

Your best option is to make sure your Date columns in the list are “Date Only”, NOT “Date and Time”

Tip 2:

Use the Format Date Function to put your dates into the same format,

Specifically Fn-FormatDate(dtSPEmptyDateValue,”M/d/yyyy”)

Tip 3:

Whenever you want to use “Today’s Date” in your workflows, do NOT assign “Today’s Date” when you create the date variable.  Instead, leave your date variable blank.  Use the Calculate date action to populate the variable.  Use the Current Date found in the Workflow Context.  This date will be formatted so you can use it to easily compare to other dates.

198217_pastedImage_1.png

Here is how to check if a date column is blank using Nintex Workflow

 

Create a blank date variable. Populate the date variable using this date: 1/1/0001 12 am 00

198218_pastedImage_2.png

Add a Set A Condition action in the workflow comparing:

 

Fn-FormatDate(dtSPEmptyDateValue,”M/d/yyyy”)

And

Fn-FormatDate(dtShip2,”M/d/yyyy”)

 

(See how we reformatted the dates to the same format?)

 

Like this:

198219_pastedImage_3.png

Where

fn-FormatDate(dtShip2,"M/d/yyyy")

Equals

fn-FormatDate(dtSPEmptyDateValue,"M/d/yyyy")

If the action evaluates as TRUE, then the date you are comparing to your Empty Date Value is blank.

 

For your specific case, we are assuming Ship1 has a value.

 

If that is the case you can do this:

Set SQLShipDate = Ship1

Get Ship2 date from the list and put it into a variable, such as dtShip2.

Create an empty date variable called dtSPEmptyDateValue.

Use the Modify Workflow Variable to populate the dtSPEmptyDateValue using this date: 

1/1/0001 12 am 00 [see above example]

Add the Set a Condition action – Compare dtShip2 and dtSPEmptyDateValue

Condition: If any value equals another value

Where

fn-FormatDate(dtShip2,"M/d/yyyy")

Equals

fn-FormatDate(dtSPEmptyDateValue,"M/d/yyyy")

If the dates are equal, do not update SQLShipDate.  If they are NOT EQUAL, set SQLShipDate to the value of dtShip2.

 

 

Alternate Solution

 

Compare the 2 ship dates:

198220_pastedImage_4.png

If dtShip2 > dtShip1 is TRUE, set SQLShipDate to dtShip2.

Else set SQLShipDate to dtShip1

 

 

You can also use the DateDiff function to compare the dates that are formatted the same

fn-DateDiff(fn-FormatDate(dtShip2,”M/d/yyyy”), fn-FormatDate(dtShip1,”M/d/yyyy”))

 

More Resources

These links will help you with formatting dates:

Standard Date and Time Format Strings

http://msdn.microsoft.com/en-us/library/az4se3k1.aspx

Custom Date and Time Format Strings

http://msdn.microsoft.com/en-us/library/8kb3ddd4.aspx

Userlevel 5
Badge +12

Hello, although you abandoned the effort to do this within Nintex, provided a pretty comprehensive method by which you should be able to carry out this task.  Would you mind if her answer was marked as correct as to direct others to the solution?

Reply