Posted by aj on 10/12/07 18:09

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@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]