Values In Timestamp Field Displayed In Hex (0x000000000000000866) On Microsoft SQL Server 2008

Question

I have a table where I save emails that have been sent. I decided then to add a TimeStamp field to this table so I can track when the e-mail had been sent. Data is being written to the table with out any issues, but when I go to view the table contents using Microsoft SQL Server 2008 Management Studio, the data contained within the Timestamp field is displayed like this: 0x000000000000000000845, even in records that have been written to the database since the Timestamp value was introduced

I then changed the field type to datetime, and it then displays a date. But it displays the date 1900-01-01 00:00:23 for example. I then changed it back to the Timestamp field, and it returned back in to it's current Hexadecimal format.

Am I doing anything wrong?

Cheers

Answer 1

I decided then to add a TimeStamp field to this table so I can track when the e-mail had been sent

Ah yes. Reading teh database would have shown you that the TMIestamp field - which is a legacy from Sybase server -does NOT store a timestamp. Basically it is something like a global operations counter. It has NO relation to time.

If you want a real timestamp, put in a DateTime type of column and set the system time as default / through atrigger etc. Timestamp is totally unsuiteable for that.

Again, no a MS thing - MS SQL Server started as Sybase SQL Server port for windows, and the Timestampdata type is a Sybase legacy.