So, as I have promised – some neater code for exact datetime handling in NAV.
The same concept, as described in the earlier post. I am going to use text string to store formatted and sortable datetime value. The only difference – I’m employing a power of .NET to do so. .NET enables me to utilise culture, and most importantly CultureInfo.InvariantCulture. Why would I do so? Simply because initial root of issue was the fact that I was in need to accept by NAV a very accurate datetime from external sources. I can hardly imagine a situation when I would need exact datetime in NAV. Dealing with data from other app which are much more accurate in terms of time handling comparing to NAV perhaps is only case when we need to deal with milliseconds.
So, then the goal is to store data within NAV SQL database with high level of accuracy and same time to allow external system to read and write this data.
And this is simple: two new functions – FormatDT and EvaluateDT – and this is pretty much it.
The first one, FormatDT simply accept datetime value. I am assuming my datetime come from webservice, therefore always UTC. So I just apply to it CultureInfo.InvariantCulture and convert it to string. Therefore, it saved in SQL DB in UTC, but as string. Same as NAV handle dates (yes, dates are always UTC in NAV SQL DB and only formatted to local time on UI). So then in DB we have an UTC date string formatted as sortable datetime. Like this one: ‘2017-08-31T19:58:29.2780000Z’
FormatDT(DateTimeString : DateTime) : Text DotNetDateTime := DateTimeString; EXIT(DotNetDateTime.ToString('o', CultureInfo.InvariantCulture));
The next task then is to evaluate it back to datetime to present it on my SOAP service.
The advantage of .NET here is the fact that TryParse method will inherit a parent culture. So, on SOAP service it will always be UTC, as NAV SOAP services have invariant culture.
On NAV UI (windows client, web client, phone, tablet, etc..) this function will inherit the client culture, which is in turn inherited from local machine. Pretty, isn’t it?
EvaluateDT(DateTimeText : Text) : DateTime DotNetDateTime := DotNetDateTime.DateTime(0); DotNetDateTime.TryParse(DateTimeText, DotNetDateTime); EXIT(DotNetDateTime);
So conclusion then:
- Datetime stored as text string allowing us a microseconds accuracy, micro, not even milliseconds and stored in SQL DB in UTC, same way as standard NAV would store dates/times in UTC.
- Datetime evaluated for UI or SOAP service same way as standard NAV would do.
A test objects here: