Timestamps vs Datetime data types

By Bill Graziano on 28 November 2000 | 5 Comments | 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.

Discuss this article: 5 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

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

CHECKDB Failing (9 Replies)

Turning Rows into Columns (10 Replies)

sql query help eith grouping sets (2 Replies)

SQL Server 2008 in Ilford UK (3 Replies)

Check if a primary keys exists (4 Replies)

SSRS Matrix Report (2 Replies)

Get Mode Average? (2 Replies)

Remote Connection Problem (2 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -