Posted by Roy Harvey (SQL Server MVP) on 10/12/07 17:56

What exactly is the data type of the column msgdate? You mention it
is a 'datetime' column. If it is the datatype of datetime then forget
about strings, it is stored internally as a couple of numbers. With a
non-zero time the standard way to test for a specific date is:

WHERE msgdate >= '20071002' AND msgdate < '20071003'

Note that the second date is the next day. This approach allows use
of an index is one is available and otherwise makes sense.

Roy Harvey
Beacon Falls, CT

On Fri, 12 Oct 2007 10:01:32 -0700, jdrake@living-dead.net wrote:

>Hi,
>
>I have a large table with a 'datetime' column that has date and time
>values in it. The data is in this format:
>
>2007-10-02 09:54:00.000
>
>The table is called 'profile' and the column 'msgdate'
>
>I want to return only rows that match a specific date. So far I have
>the following query working:
>
>select * from profile where msgdate like '%2007%'
>
>This returns all rows that start with '2007'. However I cannot seem to
>ge the syntax that will allow me to return a specific date, e.g.
>2007-10-02
>
>I have researched this, trying all sorts of queries with escape
>characters/sequences because of the dash character, but I cannot get
>it to return anything. Most of my queries have ran without error, its
>just that no data is returned.
>
>
>James

[Back to original message]