locked
timestamp or rowversion RRS feed

  • Question

  • Hi,
    I am reading information of timestamp type in sql server.
    I have read something about timestamp is deprecated and new type is rowversion, but in my sql server 2005 there isnt any type rowversion.

    is timestamp deprecated?
    how can I cast timestampt to datetime?


    Thanks
    Monday, March 26, 2007 11:34 AM

All replies

  • It is the name timestamp that is being deprecated, rowversion is another name for the same field type but is a more accurate description of the field type and should be used instead.

    The field contains nothing to do with the time but rather it contains a sequence number that is guaranteed to increase over time and indicate the order of changes in rows within the database.  It is a database level identity type value.  It is used to to determine which records have changed since a particular point.  The last value used in the current database can be found using @@DBTS.

    This means that it cannot be cast to a datetime.

    Monday, March 26, 2007 2:51 PM
  • Actually, you can cast @@DBTS to a datetime - it is relatively meaningless since it will be the number of milliseconds since midnight on Jan 1, 1900.
    Monday, March 26, 2007 4:24 PM
  •  Arnie Rowland wrote:
    Actually, you can cast @@DBTS to a datetime - it is relatively meaningless since it will be the number of milliseconds since midnight on Jan 1, 1900.

    I'm not convinced that is true as the rowversion has to be unique within a database even if it performs more than one row update in a millisecond.  So whilst historically that might have been the case it is not the case with the current MS implementation.  That is why they are trying to change the name used for the datatype to remove the misapprehension about the contents of the column.

    Whilst you can cast the contents to a datetime (it is an 8-byte integer after all) the value is meaningless as a datetime.  The original value has no date/time information in it.  The value of @@dbts in my test Master database is 0x0000000000000DAC.  That is only 3500.

    Monday, March 26, 2007 4:38 PM
  • Dhericean, we are in agreement.

    My point was to illustrate that while someone may, under misguided intentions, use CAST() on a timestamp/rowversion datatype, that the results are meaningless.

    I've seen too many folks miss the point and attempt to use timestamp/rowversion as datetime.

    Monday, March 26, 2007 5:07 PM