How do I convert SQL Timestamp from hexadecimal to date and time in NAV 2015?
In sales line, I created a new field “Date and Time” with BigInteger data type. However, SQL Timestamp property is not available in nav 2015 version. How do I proceed with the displaying of the date and time of update in sales line?
Thank you in advance.
Well you dont Simply becase the SQL Timestamp is not a date and time. It is a common mistake that most people do.
The SQL Timestamp is merely a versioning of a record in SQL and cannot be converted into a date and/or time. In the most current SQL versions the field is renamed to ROWVERSION in order to remove the confusion.
Thomas Hejlsberg wrote this some time back:
Ok, let me be more precise. Timestamp and rowversion are exactly the same thing on SQL Server. The unfortunate situation is that ISO standards describe SQL Timestamps as something related to date/time whereas MSSQL server decided to implement this as a simple counter. DB2, Oracle and other relational databases uses an actual timestamp (point in time) for timestamp.
Over time this has caused some confusion since developers using other databases as “used” to be able to extract date/time info from a timestamp, which is not possible on MSSQL.
Therefore MSSQL introduced the more correct name Rowversion to describe the same thing.
Furthermore the syntax when creating a table with a timestamp differs a litte from syntax creating a table with at rowversion (name of the column not needed when using timestamp)
Alright. Thank you. I used the CURRENTDATETIME instead and put it in the onmodify trigger. [emoticon:c4563cd7d5574777a71c318021cbbcc8]
if you use SQL scripts you no need to generate Timestamp, because MS SQL generate it from ownsite.
You need to move fileds which you can see from Object Designer.
As written above… You cannot convert a SQL Timestamp to a datetime because it has nothing to do with a date and time but a version. This has causing som much confusion that Microsoft has decided to call the field Rowversion in never SQL versions.
Microsoft has In never versions of NAV added the possiblity to see/get the SQL Timestamp/Row Version inside NAV. You can read about it here:
Palle, thanks for answer (please, read that I wrote), but I wrote to RubiRotairo - we no need to handle this field => we no need to create|update|etc field with Timestamp on SQL-level. If you use SQL-script then you shuold skip this field inside it.
Sorry, but there is an old saying “Only a fool starts to manipulate his Navision via SQL scripts!” [emoticon:4191f5ee34e248a29fa0dbe8d975f74a]
Exactly what is that that you want to change/update on SQL level using a SQL script? Generally you should never change your NAV database directly via SQL.