TIMESTAMP or DATETIME on SQL Server 2000 ?

Mmm ... when you're working on SQL Server 2000 database and you want to choose a field type between timestamp or datetime, it may this article helping you on thinking about that datatype. Unfortunately you really can't use TIMESTAMP for any type of comparison. Or really for much of anything.

This is the quote from SQL Server Books Online :

The SQL Server timestamp data type has nothing to do with times or dates. SQL Server timestamps are binary numbers that indicate the relative sequence in which data modifications took place in a database. The timestamp data type was originally implemented to support the SQL Server recovery algorithms.

It further states Never use timestamp columns in keys, especially primary keys, because the timestamp value changes every time the row is modified.

It's very strange. The timestamp value changes every time the row is modified. So, it's better if you using a DATETIME or SMALLDATETIME column in this case. DATETIME columns can store dates from January 1st, 1753 through December 31st, 9999 (there's that Y10K problem) and are accurate to roughly 3 milliseconds. They use 8 bytes of storage. SMALLDATETIME columns can store dates from January 1st, 1900 through June 6th, 2079 and are accurate to the minute. SMALLDATETIME columns only use 4 bytes of storage.

You can insert values into DATETIME columns (or SMALLDATETIME) columns by enclosing them in quotes.

INSERT tblSales (salesDate) VALUES ('4/12/2006')

This will insert the date part with the time set to midnight (12:00:00 AM). You can insert the current system date and time using the GETDATE() function:

INSERT tblSales (salesDate) VALUES ( GETDATE() )

Your SELECT statement from above might look something like this:

SELECT * FROM tblSales WHERE salesDate = '2006-12-04'

This will run fine if you are putting dates in with no times. If you are adding times and want all the records for a particular day you can do something like this:

SELECT * FROM tblSales WHERE LEFT ( CONVERT ( varchar, salesDate, 120 ), 10 ) = '2006-12-04'

Using the CONVERT function makes SQL Server very picky about formats though (since that's what CONVERT does).