Nintex Connect

I am querying a list to retrieve information about an item, e.g. let's say invoices for the purposes of this post.  When I query this list using the "Query List" action, and choose to retrieve multiple field values into variables, it retrieves all field values correctly EXCEPT for dates which are returned in mm/dd/yyyy format, instead of dd/mm/yyyy format.  The site's regional settings have been set to English (Australia).  This causes a huge issue in a "Set a condition" action that follows, when doing a date comparison.

When comparing dates in the set a condition action, the date comparison doesn't work properly because the format is wrong.  The test is looking to see if the returned due date of an invoice is before 1st of July 2012.  If due date of the invoice is 3rd September 2012, it should be returning "false" as 3/9/2010 is greater than 1/7/2012.  However, it's returning "true" is the date being returned from the query list action is "9/3/2012", which the subsequent set a condition action is treating as the 9th of March 2012, which is incorrect.  I have confirmed this by writing the returned date of the invoice to the history list immediately after performing the query list action.

We seem to have found a workaround.  The only way to get it to retrieve the date correctly is to perform two queries - one to retrieve information about the invoice EXCEPT due date, and then to query again with exactly the same query, but this time ONLY retrieving due date.  So that essentially means that the workflow is performing two queries instead of one. Not great from a performance point of view.  What we seem to have discovered is that if you ONLY return one field in a query list action, i.e. a date field, then the date is returned in the correct format, and the subsequent "set a condition" action works as it should, because the date format is returned correctly from the second query list action.

We're using NW 2010 Version: 2.3.4.0.  Is this is bug that has been fixed, or is scheduled to be fixed in an upcoming release?

Thanks.

I have a similiar problem, when I query my list i want to filter and show only elements where the "Reviewdate" in my column is overdue. I tried all possibilities with "less than", "greater than", less or equal than" and so on but i dont get any results. When i disable filtering i want to get the IDs in a Collection.

 Does anybody have news why the "Query List" Action is acting like this?

Hi all,

I had the same issue with dates not displaying/ caluclating properly. I had two dates (start date & expiry date) which I entered manually, and three dates calculated on set periods from the expiry date on each contract item in a document library. The dates display in dd/mm/yyyy format in the SharePoint Library and I was able to successfully create a calendar event for each of these dates (dd/mm/yyyy format) in a seperate workflow.

However, I then needed to query this calendar list for events due [Today], and display the other key dates in an email. Some of these dates looked like they were fine and others returned values such as 01/01/0001 or 01/01/1900. My research led me to believe this was because my date fields were blank (which made no sense) or that Nintex couldn't read them (which made no sense to me either). Initially I thought it was an issue with one of my library columns but then on some test items it seemed to affect other columns as well. I was mainly using 01/01/2012 and 31/12/2012 as my test start and expiry dates which is why it took me several days to figure out that they were being displayed in the wrong format. The first one looked like it was working fine becasue there was no difference between dd/mm and mm/dd whereas the second date failed. I then came across this post.

I tried leed's solution by using one query for all the other data I needed and a seperate query for the dates (individual and group) but this didn't work for me. After trying different actions in Nintex (including trying to build a string which swapped the months and days around which didn't work due to an incorrect format error), I used one query for all the other data and then used a "Calculate Date action" (in a parallel action) on each of the five dates. Even though this calculate date action didn't actually have a calculation, the "Time is: Local/ UTC" field must force the SharePoint locale settings on it which solved my problem.

Hope this is of help to someone.....

Hi all,

I have users having different local settings of various countries in Asia, EMEA or Americas configured in their Sharepoint 2010 access. Whenever an Inline date function like fn-DateDiffHours is used, a workflow error occurs if the user locale settings is different from the site locale.

Here's a workaround on this date formatting issue.

1. Create a Single line text variable.
2. Then use the Calculate Date (under Operations) with date assigned to your date related fields or other variables. Leave all other values as default.
3. set the Store ISO 8601 date string to your newly created text variable.

Now you can use this text variable with any Inline date functions.

Hope this helps.

Cheers..