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
[quoted text, click to view] 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
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
[quoted text, click to view] 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.
>
>
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
[quoted text, click to view] <jdrake@living-dead.net> wrote in message
news:1192208492.248548.49030@e34g2000pro.googlegroups.com...
> 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
>
Don't see what you're looking for? Try a search.
