none
How to convert a timestamp into dateTime in SQLServer 2000

    Question

  • Hi Everyone,

     

    We have a table in SqlServer 2000 with a column type TimeStamp and contain value such as 0x00000000656AC51F. Are there any way for me to convert that value back to DateTime? I tried to use cast function like:

    Select cast (MyTimeStampCol as DateTime) myDate from MyTableand I encountered error below.

     

    Msg 8115, Level 16, State 2, Line 1

    Arithmetic overflow error converting expression to data type datetime.

     

    Regards,

    JDang

     

    Monday, June 12, 2006 7:05 PM

Answers

  • Timestamp data type in SQL Server is already announced for deprecation. You should use rowversion in your code as far as possible instead of timestamp. Rowversion is also the ANSI SQL standard name / data type. Currently, we still convert rowversion to timestamp and store it in expressions or column definitions etc but if you use rowversion in your script/code it should be fine. In a future version of SQL Server, we will deprecate the use of timestamp completely. Hope this helps. Also, if you want to file a bug or suggestion use http://connect.microsoft.com. The MSDN Product Feedback Center is no longer the mechanism.
    Thursday, July 13, 2006 7:06 PM

All replies

  • Hi,

    As far as I am aware this is not possible. The reason for this, I believe, is:

    The timestamp data type in SQL Server 2000 is not a representation of any date but simply a generated binary number that us guaranteed to be unique in the database. In fact, timestamp in the SQL Server rpdocut is a synonym (basically equivalent to) the rowversion data type in the ANSI (or is it ISO) standard for the SQL language (doubling up, sorry )

    It is a tad confusing because people will see a timestamp data type and it is easy to think that it is some representation of a date but it is not. As an aside, there is a timestamp data type defined in the standard for the ANSI/ISO SQL language but this is equivalent to DateTime in the SQL Server product.

    Hope that helps or clears up the issue a bit.
    • Proposed as answer by 'juls' Tuesday, July 26, 2011 7:22 AM
    • Unproposed as answer by 'juls' Tuesday, July 26, 2011 7:22 AM
    Tuesday, June 13, 2006 9:00 AM
  • Hi John,

    I agree with Nate's suggestion.

    The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ta-tz_6fn4.asp

    We can consider it as a rowversion or GUID which SQL server have its algorithm to generate it. But it is not a DateTime, we can not convert them from each other because they are different things.

    If you still have any concern, please feel free to post here.

    Best regards,
    Peter Huang

    Tuesday, June 13, 2006 9:08 AM
  • Thanks for your help. Before I come back to SQLSever, I have done a lot with timestamp in oracle and it is a DateTime data type. Personally, I think Microsoft should rename "timestamp" datatype to something else like RowID since we cannot convert back to date and time.

    Regards,

    Jdang

     

    Wednesday, June 14, 2006 6:29 PM
  • Hi John,

    Thanks for your feedback.
    I do understand your concern in this scenario, I highly suggest you can
    submit this feedback to our product feedback center:
    http://lab.msdn.microsoft.com/productfeedback/default.aspx

    Thanks for your understanding!

    If you still have any concern, please feel free to post here.

    Best regards,
    Peter Huang

    Thursday, June 15, 2006 5:35 AM
  • Hi, as we know that timestamp in SQL server is to control the concurrencies.

    Is there any way to compare 2 timestamp in .NET?

    For eg. I got 2 timestamp, I want to compare it so that I know which timestamp is the latest 1. Means the most recent changed.

    Thanks.

    Thursday, July 13, 2006 5:51 AM
  • Timestamp data type in SQL Server is already announced for deprecation. You should use rowversion in your code as far as possible instead of timestamp. Rowversion is also the ANSI SQL standard name / data type. Currently, we still convert rowversion to timestamp and store it in expressions or column definitions etc but if you use rowversion in your script/code it should be fine. In a future version of SQL Server, we will deprecate the use of timestamp completely. Hope this helps. Also, if you want to file a bug or suggestion use http://connect.microsoft.com. The MSDN Product Feedback Center is no longer the mechanism.
    Thursday, July 13, 2006 7:06 PM
  •  

    SELECT convert(datetime,[timestamp_Column_Name],101) as TS

    from test

    • Proposed as answer by kicasd Monday, August 03, 2009 2:04 PM
    Friday, April 11, 2008 5:27 PM
  • You can convert to a (kind of) date format using

    SUBSTR(XXX.TIME_STAMP, 7, 2)||'/'||SUBSTR(XXX.TIME_STAMP, 5, 2)||'/'||SUBSTR(XXX.TIME_STAMP, 1, 4)

    You can then compare that to a date using

    TO_CHAR(XXX.DATEFIELD, 'DD/MM/YYYY')

    And putting both in your where clause

    So

    WHERE

    SUBSTR(XXX.TIME_STAMP, 7, 2)||'/'||SUBSTR(XXX.TIME_STAMP, 5, 2)||'/'||SUBSTR(XXX.TIME_STAMP, 1, 4)

    =

    TO_CHAR(XXX.DATEFIELD, 'DD/MM/YYYY')

    A nice little trick i use :)


    • Edited by 'juls' Tuesday, July 26, 2011 7:24 AM FORMATTING
    Tuesday, July 26, 2011 7:22 AM
  • This seems to work:

    declare @x timestamp
    set @x = CAST(GETDATE() as timestamp)
    select @x
    select CAST(@x as datetime)

    ---

    Why does SQL Server 2008 R2 Management Studio only provide the "timestamp" option and not the "rowversion" option when creating a new column if the former is being phased-out?

    Wednesday, July 27, 2011 5:58 AM