SQL Server hexadecimal handling

Question

I believe I'm having a problem with the way SQL Server 2000 handles hexadecimal numbers.

If I do a

select * from table where [timestamp] = 44731446

the row that it returns shows the timestamp as 0x0000000202AA8C36

Equally in another table if I

select * from table2 where [timestamp] = 44731446

the row that it returns shows the timestamp as 0x0000000002AA8C36 (notice the missing 2)

MS Calc tells me that the first timestamp = 8634666038 in decimal and the second timestamp = 44731446 in decimal which matches my original query on both tables.

So why is SQL Server returning a different number, yet successfully querying it? I believe this is the route of an update problem I'm having where the row won't update.

What data type is your timestamp field? Is the the timestamp data type? — JNK
– JNK, Commented Nov 8, 2010 at 15:53
I got an idea that it was something to do with the datatype being too small (and truncating the extra 2). I changed the timestamp datatype to int and it appears to work fine now. Any ideas why? — m.edmondson
– m.edmondson, Commented Nov 8, 2010 at 16:08

Pondlife · Accepted Answer · 2010-11-08 16:10:48Z

Long story short, the binary to integer conversion is truncating data:

select cast(0x0000000202AA8C36 as int)

A TIMESTAMP column is really BINARY(8), so your query is comparing a BINARY(8) value to an INT value; because INT has the higher precedence, MSSQL converts the BINARY(8) value to INT before comparing them.

But, 0x0000000202AA8C36 (or 8634666038) is too big to be represented as INT, so MSSQL has to truncate it first, and it truncates to the same value as 0x0000000002AA8C36. This might be a little clearer:

create table dbo.t (tstamp binary(8) not null)
go

insert into dbo.t values (0x0000000202AA8C36)
insert into dbo.t values (0x0000000002AA8C36)
go

-- returns 2 rows
select * from dbo.t where tstamp = 44731446
-- returns 1 row
select * from dbo.t where tstamp = cast(44731446 as bigint)
go

drop table dbo.t 
go

According to Books Online (for 2008, I don't have 2000):

When [non-string data types] are converted to binary or varbinary, the data is padded or truncated on the left. Padding is achieved by using hexadecimal zeros

Collectives™ on Stack Overflow

SQL Server hexadecimal handling

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related