Timestamp DataType

[JustATheory]

Greetings,
For other data types I can run this query no problem, I’m having trouble with the “timestamp” data type. I’m looking for the record between max and min values based on timestamp value; there will always be one record between the two. For date_time I’ll write a having clause:
Having min_date = max_date-1

I’ve not worked with timestamp data type much, it is possible that the value would be within the same day. I know there will be three records with three different timestamps and I’m looking for the record in the middle.

Any help would be greatly appreciated
Thanks,
Andy

 

[philhege]

You understand of course that the timestamp data type does not represent a date value. The value of the timestamp field changes every time the row is updated.

Paste the following link in BOL (2005) for more information:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/65c9cf0e-3e8a-45f8-87b3-3460d96afb0b.htm

-------++NO CARRIER++-------

 

[TheBugSlayer]

Hmmm...Try something I learned from George:

DECLARE @MinDate VARCHAR(23)
DECLARE @MaxDate VARCHAR(23)

SELECT @MinDate = '1900-01-01 00:00:06.683'
SELECT @MaxDate = '1900-01-01 00:00:06.690'

SELECT * 
FROM YourTable 
WHERE DATEADD(ss, 0, TimeStampColumn) > @MinDate AND
      DATEADD(ss, 0, TimeStampColumn) < @MaxDate

Good luck.

MCITP SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)