NAV System table 'Date' strange filtering

Roelof

We recently did a test upgrade from NAV2015 to NAV2017 and doing some testing of custom functionality. We noticed a strange behavior of the NAV System table 'Date'. Apparently the table does not filter correct on the boxes of the customer, while this behavior is not occurring on our test-server.
Here is a brief explanation of the issue:

Code:
UseOrderDate := 041417D;
Date.SETRANGE("Period Type",Date."Period Type"::Date);
Date.SETRANGE("Period Name",'Friday');
Date.SETFILTER("Period Start",'>=%1',UseOrderDate);
IF Date.FINDFIRST THEN
MESSAGE('Date = %1',Date."Period Start")
ELSE
MESSAGE('Date not found.');

Results:
- On our server the result is correct, as:
'Date = 04/14/2017.

- On the customer server (we tried a few different boxes, but same result):
'Date = 01/03/0001'.

According to the Customer's IT person and SQL specialist this behavior is not related to any SQL server setting.
The only difference is that we use 'SQL-2012' as our test server while our customer is using 'SQL-2016'.
I would assume that SQl-2016 is compatible with NAV2017. (build: 10.0.14199).

Any ideas?

Juhl

Localization???

vytjak

Yes, as Juhl pointed out - the issue might be localization. The code sets the Period name filter as 'Friday'. If the regional setting is is set to not an English speaking country, chances are that the period name 'Friday' does not exist.

Kishorm

Try restarting the NST to see if this fixes the problem. I have encountered a very strange bug which crops up very occasionally (i.e. not often) which results in similar behaviour. This isn't limited to 2017 though, it's been happening through various versions of 2016 too.

If you can consistently replicate the issue then I'm sure Microsoft would be interested in hearing from you so it would be very worthwhile raising a support issue with them.

Kishorm

vytjak wrote: »

Yes, as Juhl pointed out - the issue might be localization. The code sets the Period name filter as 'Friday'. If the regional setting is is set to not an English speaking country, chances are that the period name 'Friday' does not exist.

If this was a localisation issue and 'Friday' was not a valid "Period Name" then you would expect no records to be found and therefore the 'Date not found' message to be shown.

NavNab

Hi Roelof,

I suggest you to test if the code below will help you:

// UseOrderDate := 041417D; // help the code to understand it is 2017
UseOrderDate := 04142017D;
Date.SETRANGE("Period Type",Date."Period Type"::Date);
// Date.SETRANGE("Period Name",'Friday'); // use "Period No." instead
Date.SETRANGE("Period No.", 5);
Date.SETFILTER("Period Start",'>=%1',UseOrderDate);
IF Date.FINDFIRST THEN
MESSAGE('Date = %1',Date."Period Start")
ELSE
MESSAGE('Date not found.');