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:

1. 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.
2. Datetime evaluated for UI or SOAP service same way as standard NAV would do.

Job done.

A test objects here:

 

My data have a timestamp. A datetime datatype. And it is vital to me as business process dictates so some logic decisions must be made based on record datetime.  What I have noticed is that all my datetimes ends with 0 or 3 or 7 milliseconds. Like 01/01/2017 20:54:253. Or 01/01/2017 20:54:250. Or 01/01/2017 20:54:257. But never 01/01/2017 20:54:254.

Quite strange, as I have assumed the tick is a millisecond. Which is true in NAV – the smallest tick is a millisecond (1/1000 second) indeed. So why then always 0 3 or 7 in the database? I quickly created a nav code like this:

OnRun()
MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyTable."Create Date" := MyDatetime + i;
  MyTable.INSERT;
END;

What would you expect of this code? I was expecting 1000 records created with one tick datetime difference. I’m using CLEAR(MyTable); as my table has autoincrement primary key, so MyTable.INIT would not work as INIT does not clearing the primary key value, so INSERT will fail then as we let SQL server to decide primary key.

And indeed, I had 1000 records. And all of them ends with 0, 3 or 7. Mystery? There must be no mystery in IT. So I amended the code then to dump data to simple text file same time as I am inserting to table.

MyFile.TEXTMODE := TRUE;
MyFile.WRITEMODE := TRUE;
MyFile.CREATE('c:\temp\test.txt');
MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyTable."Create Date" := MyDatetime + i;
  MyTable.INSERT;
  MyFile.WRITE(FORMAT(MyTable."Create Date", 0, '<Day,2>/<Month,2>/<Year4> <Hours24,2>:<Minutes,2>:<Seconds,2><Second dec.><Comma,.>'));
END;
MyFile.CLOSE;

And results were truly surprising.

Text File	SQL table
30/08/2017 11:58:29.271	30/08/2017 11:58:29.270
30/08/2017 11:58:29.272	30/08/2017 11:58:29.273
30/08/2017 11:58:29.273	30/08/2017 11:58:29.273
30/08/2017 11:58:29.274	30/08/2017 11:58:29.273
30/08/2017 11:58:29.275	30/08/2017 11:58:29.277
30/08/2017 11:58:29.276	30/08/2017 11:58:29.277
30/08/2017 11:58:29.277	30/08/2017 11:58:29.277
30/08/2017 11:58:29.278	30/08/2017 11:58:29.277
30/08/2017 11:58:29.279	30/08/2017 11:58:29.280
30/08/2017 11:58:29.280	30/08/2017 11:58:29.280

 

As you can see – in the file it is one tick 1/1000 of second, as expected. But in SQL the tick is about 1/300 second and rounded to nearest. Thank you, MS, for your infinite wisdom!

I can prove the point – https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond

Of course, there is a SQL datatype datetime2 with precision of 100 nanoseconds. But it is not available in NAV.

So, then conclusion: never use NAV datetime if you want one tick precision. It is not here. Yes, NAV can handle 1/1000 of a second, but SQL cannot!

To prove the point, I have added two new fields to my table: Create Date and Create Time, with NAV datatypes Date and Time respectively. And slightly amended the code

MyDatetime := CURRENTDATETIME;
FOR i := 1 TO 1000 DO BEGIN
  CLEAR(MyTable);
  MyDatetime += 1;
  MyTable."Create DateTime" := MyDatetime;
  MyTable."Create Date" := DT2DATE(MyDatetime);
  MyTable."Create Time" := DT2TIME(MyDatetime);
  MyTable.INSERT;
END;

I somehow was under impression that on SQL end NAV Time datatype handled differently. But it is not. On SQL end NAV Time = SQL datatype DateTime. Which is rounded to 1/300 of a second.

So, there is no way in NAV 2016, 2017 or any other version of NAV to record a time with precision of one tick, I mean 1/1000 second. No way.

Well, there is. A text string of length 23 and some stupid weird code to format datetime received from external sources (where it is high-precision) to string, and back to datetime when our external source read nav data via webservice. I know, it is ugly. But I cannot see better way.

DatetimeToString(MyDatetime : DateTime) : Text
IF MyDatetime = 0DT THEN
  MyDatetime := CREATEDATETIME(01011753D, 000000T);
String := FORMAT(MyDatetime, 0, '<Day,2>/<Month,2>/<Year4> <Hours24,2>:<Minutes,2>:<Seconds,2><Second dec.><Comma,.>');
IF STRLEN(String) = 19 THEN
  String := String + '.';
IF STRLEN(String) < 23 THEN
  String := PADSTR(String, 23, '0');
EXIT(String);

StringToDatetime(MyDatetimeAsString : Text) : DateTime
EVALUATE(Day, COPYSTR(MyDatetimeAsString, 1, 2));
EVALUATE(Month, COPYSTR(MyDatetimeAsString, 4, 2));
EVALUATE(Year, COPYSTR(MyDatetimeAsString, 7, 4));
EVALUATE(MyTime, COPYSTR(MyDatetimeAsString, 12, 12));
MyDate := DMY2DATE(Day, Month, Year);
MyDatetime := CREATEDATETIME(MyDate, MyTime);
EXIT(MyDatetime);

And there is another issue – the datetime stored in NAV always stored in UTC and then formatted to local time on NAV UI. On webservice it is UTC though. So, then all this handling needs to be implemented in your code when you do format datetime to string and vice versa, or to be precise, when you display this data in NAV.

I will cover this in separate post though.

UPDATE: I actually see a better way of managing this. Still using strings, but utilizing the power of .NET – so cleaner and faster code then. New post will follow on this.