none
Query timestamp column RRS feed

  • Question

  • Hi all,

    My table has a column called LastChanged which data type is timestamp, this column records row modify time, but when I try to query this table which has hundreds of records with this command:

    select * from dbo.KEHU where LastChanged>convert(timestamp, '0x0000000000000000') order by dbo.KEHU.LastChanged desc;

    the query result is empty without any error message.

    Anybody please give me a tip.

    Tuesday, January 2, 2018 8:52 AM

Answers

  • '0x0000000000000000' is a string literal. If you convert it to binary(8) you will get a value which is likely to be a lot greater than @@DBTS.

    Skip the quotes to get a binary literal. And you don't need the convert.

    Tuesday, January 2, 2018 9:06 AM
  • Ideally you would create these type of logic based on a timestamp value extracted from the table itself from a point in time

    i.e say suppose you want all rows from a point in time

    you can use like

    DECLARE @Timestamp binary(8)
    
    SELECT @Timestamp = MIN(timestampcolumn)
    FROM Table
    WHERE ....
    
    
    SELECT ...
    FROM dbo.KEHU
    WHERE LastChanged > @Timestamp
    ...

     

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 2, 2018 9:16 AM

All replies

  • '0x0000000000000000' is a string literal. If you convert it to binary(8) you will get a value which is likely to be a lot greater than @@DBTS.

    Skip the quotes to get a binary literal. And you don't need the convert.

    Tuesday, January 2, 2018 9:06 AM
  • Ideally you would create these type of logic based on a timestamp value extracted from the table itself from a point in time

    i.e say suppose you want all rows from a point in time

    you can use like

    DECLARE @Timestamp binary(8)
    
    SELECT @Timestamp = MIN(timestampcolumn)
    FROM Table
    WHERE ....
    
    
    SELECT ...
    FROM dbo.KEHU
    WHERE LastChanged > @Timestamp
    ...

     

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, January 2, 2018 9:16 AM
  • Thanks both for help.
    Tuesday, January 2, 2018 9:48 AM