Hi,

How do you convert from the 'timestamp' datatype to a varchar? I tried
variations on convert(varchar(20), timestamp, 101) to no success. All that
gets returned is a bunch of gobbledygook.

Thanks

Lance wrote:

Look up timestamp in the Sybase docs. It has nothing to do with
the human-readable time or date. The for-humans, real time and date
column is called 'datetime'.
Joe Weinstein at BEA

I understand that the timestamp is varbinary(8). I suppose I should also
ask if there is a way to make this output human-readable as well.

The reason I want to look into this column is to see which rows in a table
have been updated, and sort them from the most recent to the oldest.


"Lance" <lancestuff27 (AT) hotmail (DOT) com> wrote

On Wed, 10 Dec 2003 16:51:16 +0000, Lance wrote:

If you convert the timestamp to a varchar you'll get a hex string. You
should be able to sort on that string - although I'm not sure if that will
really get you any data that you can use direectly.

Michael

--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html

Lance wrote:

Unless you remember what they 'looked like' before, you should not be visually
comparing. These columns are for programmatic comparison. You should store the
values as byte arrays, and then compare the values to the current ones in the
table. If a value is different, you can be sure it's been changed. You should check,
but I don't think the DBMS promises that one row's timestamp will have any
relationship to another row's timestamp value. The only promise the DBMS makes
is that if a row is changed, it's timestamp value will be changed, and will be different
than any previous value that row's timestamp had. I believe it is implemented to
use the 10-byte field as a sort of increasing numerical value so you can use greater-than
comparisons between a remembered value for a row and the current value, but I may be wrong.
What you need is a datetime column, and an update trigger that updates the
datetime column.
Joe Weinstein at BEA

"Lance" <lancestuff27 (AT) hotmail (DOT) com> wrote

You can use the inttohex function to convert 4 bytes at
a time to char. I use varbinary(8) here, you can simply substitute
a timestamp as they are varbinary(8)s as well.

1> create table foo (x varbinary(8))
2> go
1> insert foo values (0x123456789abcdef)
2> go
(1 row affected)
1> select * from foo
2> go
x
------------------
0x0123456789abcdef

(2 rows affected)
1> select inttohex(substring(x,1,4)) + inttohex(substring(x,5,4)) from foo
2> go

----------------
0123456789ABCDEF

(1 row affected)


-bret