fn-FormatDate doesn't work with specific regional settings


Badge +3

Hi!

I have a DateOnly field in standard SP list, which contains date: 13.10.1988, for example
SPWeb (where my list located) have UTC+7 timezone in site regional settings (/_layouts/15/regionalsetng.aspx)
So, the UTC value in database looks like:

<datetime1>1988-10-12T16:00:00</datetime1>

Next, in workflow I'm getting date field from SQL database (using Execute SQL action) which is "date" type, looking like a '1988-10-13' and then I save it to Nintex Date and Time variable.

203904_pastedImage_13.png

And finally, I'm trying to compare (using Set a condition action) this two dates: list date vs SQL date, which are obviously equal:

203909_pastedImage_2.png

condition looks like:

203911_pastedImage_4.png

It worked nice:

203914_pastedImage_8.png

203912_pastedImage_6.png

until I've changed my user regional settings from Russian (locale 1049) to English (USA, locale 1033) and tried to re-run workflow under this user account

I discovered that inline FormatDate function doesn't work anymore:

203913_pastedImage_7.png

It looks like Nintex doesn't recognize its own inline functions and trying to compare text strings which is different.

Then I tried the same comparsion but without fn-FormatDate, just compare two variables, one from SQL, second from item.

And realized that in both cases (locale settings) I got result that these dates are different.

203921_pastedImage_9.png

LCID 1049:

   203922_pastedImage_10.png

LCID 1033:

   203923_pastedImage_11.png

I do not understand now at all how Nintex performing dates comparing. Please help me for get this to work correct )


7 replies

Userlevel 4
Badge +12

Whenever inline functions just render their content usually it's because an error occured within them. Instead of showing an error message they're just show themselves as text.

You could try the following. Create a "convert value" action for both of your dates and add the Culture into it:

203992_pastedImage_1.png

Doing this with both dates and adding the same culture should bring the date into the same date format and should make them comparable. If it doesn't work with the russian culture info maybe it helps to bring both dates into US format.

Best regards

Enrico

Badge +3

Hi Enrico! Thank you for reply

I tried to add Convert value action.

When I run workflow under account with English LCID, both dates are converted to en-us culture and compared as expected:

204023_pastedImage_1.png

The same thing under user with Russian LCID, both dates are converted to ru-ru culture (as i specified in action configuration) and all's good:

204024_pastedImage_4.png

However, when workflow trying to convert date to culture that different from initiator's regional setting, it gives me an error, both with russian and english LCID.

Trying to convert to en-us culture under initiator with russian locale:

Error parsing value. Value '13.10.1988 0:00' is not compatible with the selected output variable type Date Time.

Trying to convert to ru-ru culture under initiator with english locale:

Error parsing value. Value '10/13/1988 12:00 AM' is not compatible with the selected output variable type Date Time.

So, I don't have a unified culture to convert to..

Am i need to specify Date format while converting?

I tried to do something like that:

204025_pastedImage_54.png

and got SQL date converted to ru-ru culture:

204026_pastedImage_56.png

Now I see the only workaround is to "Run as workflow owner" when owner is account with permanent regional settings, but it's not a solution )

Userlevel 4
Badge +12

Hmm, a tricky one.

Date Format won't help you here. All it does is to check the format you enter in Date Format and check if your date matches the given format. If it's not it won't be recognized.

If you use Run as workflow owner just for the converting / comparison actions, wouldn't that solve the problem? All other actions could run as initiator.

You could also try to create a User defined action that does all the conversion and if needed have it run in workflow owners context. It could send the result of the comparison back to your workflow that afterwards keeps running with normal initiators context. Would that be an option?

Otherwise I only could think of using any kind of PowerShell action or a web service to do the locale conversion and compare the dates.

Best regards

Enrico

Badge +3

Heh, I don't want to reopen all my workflows to check this checkbox "run as owner" ) And I will need to republish all workflows under isolated account, where no-one couldn't change regional settings..

And I still can't understand, is it bug or expected behaviour..?

If it is a bug, maybe better to contact Nintex Support, how you think?

Userlevel 4
Badge +12

Agreed. They might have faced this issue before and maybe able to help in this.

If they get back with something helpful, you could update this thread to let others know there is a solution.

Badge +3

FYI, Nintex Support confirmed that they are reproduced the issue, so this is a bug, not feature )

I'm waiting for resolution from support and will give feedback to this thread later.

Badge +3

I received a reply from Support Team that currently the functionality of "fn-FormatDate" will only work with the default setting of "My Language And Region > Always follow web settings" is being checked.
Therefore, the user business requirement to uncheck this setting will not be supported and categorized as a feature request.

Probably, I will leave a feature request at User Voice. For now, this topic can be closed as unresolved )

Reply