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?
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.
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
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.
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
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.
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.
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:
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:
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:
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
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?
