i have a table with a timestamp column, and there is a record with
timestamp value 0x0000000000011174

when i execute the following select query:

select * from mytable where ts > 0x0000000000011170 --this works fine,
it returns the record,

select * from fis where version> 4467 --this also work, i think its
because
select cast(4467 as timestamp)  gives me the 0x0000000000001173 (no
typing error its 1173 in binary), and its less than 0x0000000000011174.


but the stanrange thing is:

select * from fis where version> 4468  --this doesn't work, returns
nothing, cast(4468 as timestamp) gives me 0x0000000000001174.

so, what is the problem?. 0x0000000000001174 is still less than
0x0000000000011174.
is there an issue about comparing the timestamp columns?
thank you.

Are you sure you've posted correct values?


ML

---
http://milambda.blogspot.com/

On 30 Aug 2006 04:06:10 -0700, Endo wrote:

Show quoteHide quoteHi Endo,

Well, first of all: you're not comparing timestamp columns. The constant
4468 will implicitly be typed as integer, and since integer has a higher
precedence than timestamp, the timestamp value will be converted to
integer before the comparison.

Now, if you run
   DECLARE @ts timestamp
   SET @ts = 0x0000000000011174
   SELECT @ts, CAST(@ts AS int), CAST(@ts AS smallint)
you will see that this particular timestamp value converts to 70004
(which is > both 4467 and 4468) when converted to int, but to 4468 when
converted to smallint.

What apparently happens is that SQL Server decides that smallint
suffices for the constant values 4467 and 4468 (*). Therefor, the
timestamp is also converted to smallint. This results in 4468, which is
more than 4467, but obviously not more than 4468.

The workaround for you is to use either
   SELECT * FROM fis WHERE version > CAST(4468 AS int);
or (even better since it eliminates the conversion of the timestamp
column and is therfor able to use an index on said column to speed up
execution):
   SELECT * FROM fis WHERE version > CAST(4468 AS timestamp);

(*) I have no idea why SQL Server settles for smallint. Sure, it fits
the range - but executing
   SELECT 4468 AS a INTO temp;
   EXEC sp_columns temp;
   DROP TABLE temp;
shows that 4468 is interpreted as int, not smallint in this fragment. I
don't know why SQL Server makes it's choices this way.

Making a table with multiple selects and possibly simple cases
help with query
Connection to server very slow/timeout expired error
To Select or Not To Select
Trigger PK
Re: How to detect SQL server edition
Select Value into a Variable in an SP
Reading Data in a SP w/o Returning It
Beginner - Sql Server - store URL as varchar and URL encoding
Query Join Help