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)

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.

Related Articles

Using the TIME data type in SQL Server 2008 (6 March 2008)

Using the DATE data type in SQL Server 2008 (6 December 2007)

Working with Time Spans and Durations in SQL Server (15 October 2007)

DATEDIFF Function Demystified (20 March 2007)

The Cost of GUIDs as Primary Keys (8 January 2005)

Search and Replace in a TEXT column (18 January 2004)

INF: Frequently Asked Questions - SQL Server 2000 - Table Variables (7 December 2003)

How to search for date and time values (2 May 2003)

Other Recent Forum Posts

Group by issues (2d)

Adding new foreign key column to existing table (3d)

Replacing NULL Values in Sub-Query (3d)

How to grab some code from this string (4d)

Tracking Memory Usage by an instance (4d)

Performance improvement for select query (4d)

Can a joined query be as efficient as a single table? (5d)

Neglect replication while upgrading SQL SERVER (5d)

- Advertisement -