NAV RecordIDs in SQL - decoding date from hex string

Question

I am trying to reverse engineer the Record IDs for a table, and have hit a stumbling block when I get to a date field as part of a record ID:

The recordID for one of my test records is:

0x027D89000089FF4150303130353539000089FF4D43564100002E537116000000

Which decodes as follows:

0x027D8900 is the table ID in little endian, resolves to 9010434 (my table id)

0x0089 refers to the "code" data type

0xFF indicates that my key contains characters

0x415030313035353900 is my first primary key and resolves correctly to AP010559 using a simple cast to varchar

the next section is the same for the second primary key and all resolves correctly using cast

However I am having trouble with the 002E537116000000, this refers to a date and breaks down like this.

0x002E - "date" data type

0x53711600 - the date, should resolve to 2013-06-17 but I can't work out how, note the last byte is just a termination

0x0000 - 2 null bytes

Relevant links:

NAV data types: http://blogs.msdn.com/b/nav/archive/2010/09/03/table-data-type-values-used-in-record-links.aspx

Structure of RecordID: http://blogs.msdn.com/b/microsoft_dynamics_nav_sustained_engineering/archive/2009/08/06/how-do-record-links-encode-their-data-in-sql-server.aspx

Thanks in advance for any guidance

Answer 1

Decoding a date field is indeed tricky.

It is actually a number of days since the earliest supported date (03-01-01), but multiplied by 2 (because even numbers correspond to normal dates, and odd - to closing dates), and with a small shift for special values.

Here is how it is calculated (example in PowerShell):

$days = [Convert]::ToInt32("167153", 16)
$datetime = new-object System.DateTime(((($days - 737) / 2) + 2) * [TimeSpan]::TicksPerDay)
Write-Host $datetime

Date type in NAV is stored as Int32, so the last byte is not a terminal, but the first byte of Int32 value in little endian. But, since the maximum possible date value is C31-12-9999 which corresponds to 0x92766F00, this byte is always 0 in date values.

737 is the minimum supported date (03-01-0001). Below this range are Undefined date (0D), and some other special constants like Minimum Date, Minimum Closing Date.