Understand timestamp field in HEX format in NAV table on SQL

Gabry
Gabry Member Posts: 48
edited 2013-07-24 in NAV Three Tier
Looking at NAV table on SQL Server Management Studio there is an interesting additional field called "timestamp". It seems that it it a hexadecimal format. I tried to convert it in a decimal format, but it does not represent a date format.

How can I read it?

For instance:
timestamp: 0x000000000009D302 (643842 converting it in a decimal format, but it does not seem a date format)

How do I translate it into a date format?

Comments

  • rmv_RU
    rmv_RU Member Posts: 119
    It is not date format, Nav use the timestamp as a row version.
    Because timestamp increases by 1 for every modification in database, you can use a Change Log Entry table to find when record was changed last time.
    Right now I'm checking when records in warehouse entry were changed :)
    declare @bi bigint 
select @bi=cast(timestamp as bigint) from [company$warehouse entry] where [Entry No_]=10432001
select top 10 * from [company$change log entry] where cast(timestamp as bigint)>@bi order by cast(timestamp as bigint)
    Looking for part-time work.
    Nav, T-SQL.
  • Gabry
    Gabry Member Posts: 48
    rmv_RU wrote:
    Right now I'm checking when records in warehouse entry were changed :)

    Thank you. Please lt me know about it.
  • rmv_RU
    rmv_RU Member Posts: 119
    1. Find timestamp of record which you would like to check.
    2. Find record with nearest timestamp in the Change Log Entry Table and look into "Date and Time" field

    SQL code is below:). 
    declare @bi bigint 
select @bi=cast(timestamp as bigint) from [company$warehouse entry] where [Entry No_]=10432001
select top 10 * from [company$change log entry] where cast(timestamp as bigint)>@bi order by cast(timestamp as bigint)
    Looking for part-time work.
    Nav, T-SQL.

Categories