Timestamps vs Datetime data types

By Bill Graziano on 28 November 2000 | Tags: Data Types

saka writes "Hi there, gurus, I have a really tough question regarding an sql query involving dates. I have a table (links) containing a column with dates (gdate). When I run the following statement I won't get any records at all, just the fields:

SELECT * FROM links WHERE gdate = 2000-11-05

N.B. The table do have a record with the above date and it doesn't work with another date either. The field type is dbtimestamp. Greetings from Sweden"

Ahhh. The dreaded TIMESTAMP datatype. Unfortunately you really can't use TIMESTAMP for any type of comparison. Or really for much of anything. To 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.

I'd suggest 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 Table1 (DateTimeColumn) VALUES ('6/3/2021')

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 Table1 (DateTimeColumn) VALUES ( GETDATE() )

Your SELECT statement from above might look something like this:

SELECT * FROM links WHERE gdate = '2000-11-05'

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 links WHERE LEFT( CONVERT(varchar, gdate, 120), 10) = '2000-11-05'

Using the CONVERT function makes SQL Server very picky about formats though (since that's what CONVERT does). I'd read up on CONVERT in Books Online. And greetings from America.