how to query for a column value that contains dashes

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.

JamesWhat 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:

Quote:

Originally Posted by

>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

|||Something like this (untested, but you see the point):

select <col list>
from profile
where msgdate >= @.date and msgdate < dateadd(dd,1,@.date)

date would be a parameter...

MC

<jdrake@.living-dead.netwrote in message
news:1192208492.248548.49030@.e34g2000pro.googlegro ups.com...

Quote:

Originally Posted by

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
>