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.
Solved! Go to Solution.
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.
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,
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
is not null
is greater than or equal to
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.
I've just updated your question title to reflect the post. Please try to keep the titles informative for other users on the forum.
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.
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
Comparing Dates in SharePoint
In response to your situation above, here are a few tips.
Your best option is to make sure your Date columns in the list are “Date Only”, NOT “Date and Time”
Use the Format Date Function to put your dates into the same format,
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.
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
Add a Set A Condition action in the workflow comparing:
(See how we reformatted the dates to the same format?)
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
If the dates are equal, do not update SQLShipDate. If they are NOT EQUAL, set SQLShipDate to the value of dtShip2.
Compare the 2 ship dates:
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
These links will help you with formatting dates:
Standard Date and Time Format Strings
Custom Date and Time Format Strings