querying a timestamp field...

[AWEinCA]

I get the following error when running my query:

“Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type datetime to data type timestamp, table 'trading.dbo.pricechg', column 'timestamp'. Use the CONVERT function to run this query.”

SELECT commodity_code, spot, timestamp
FROM pricechg
WHERE commodity_code = 'SILVER'
AND timestamp = (SELECT min(timestamp)
FROM pricechg
WHERE commodity_code = 'SILVER'
AND pricechg.timestamp = CONVERT(datetime,'7/16/2001'))

According to BOL I should be able to say: pricechg.timestamp = '7/16/2001'

What am I overlooking or doing wrong.

Thank you in advance for ANY help you can give.

Aaron

 

[tlbroadbent]

A timestamp column is not the same as a datetime column. You can't query it for a date. This is what BOL says about timestamp data type.

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

IMO - timestamp is misnamed. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin