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

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 (AT) living-dead (DOT) net wrote:

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 (AT) living-dead (DOT) net> wrote

The fact that the datetime column values contain dashes is incidental.
You need to query just the date part of the datetime.

Try something like this:
select * from profile
where dateadd(dd,datediff(dd,0,msgdate),0) = '10/2/2007'
---
This should work also:
select * from profile
where msgdate >= '10/2/2007' and msgdate < dateadd(d, 1, '10/2/2007')
---
Or you could persist a computed column consisting of just the date
portion of the datetime:

drop table profile
go

create table profile {
id int identity(1,1),
msgdate datetime,
justthedate as dateadd(dd,datediff(dd,0,msgdate),0) persisted,
primary key(id))
go

select * from profile where justthedate = '10/11/2007'

If you have lots of insert activity on the table, this last one might
not be a good idea..

HTH
cheers

Allen Jantzen



jdrake (AT) living-dead (DOT) net wrote: