"John Carnahan" <carnahan_j@msn.com> wrote in message
news:upfHrYvXEHA.1000@TK2MSFTNGP12.phx.gbl...
> Having a problem understanding datetime field query. When I do the
> following, I don't get the correct data.
> What am I doing wrong?
>
> this gives me everything in the table regardless of the query expression..
> SELECT MyField FROM MyTable WHERE MyDateTimeField >= '6/1/2004' AND
> MyDateTimeField < '7/1/2004'
>
> For some reason, if I do the following, I get 6/1/2004 records as well as
> records dated after 6/1/2004... Should just get 6/2/2004 and above
> SELECT MyField FROM MyTable WHERE MyDateTimeField > '6/1/2004'
>
> Do I need to do a cast or some other function in the select?
>
>

> For some reason, if I do the following, I get 6/1/2004 records as well as
> records dated after 6/1/2004... Should just get 6/2/2004 and above
> SELECT MyField FROM MyTable WHERE MyDateTimeField > '6/1/2004'

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:edCy2VxXEHA.3564@TK2MSFTNGP11.phx.gbl...
> > For some reason, if I do the following, I get 6/1/2004 records as well
as
> > records dated after 6/1/2004... Should just get 6/2/2004 and above
> > SELECT MyField FROM MyTable WHERE MyDateTimeField > '6/1/2004'
>
> No, that is incorrect. DATETIME contains time information too. 6/1/2004
> 4:00 PM is > 6/1/2004 at midnight, which is what your > is comparing to.
If
> you only want rows from 2004-06-02 and later, then use >= '20040602'
>

"Andrea Montanari" <andrea.sqlDMO@virgilio.it> wrote in message
news:2kleneF3m5u4U1@uni-berlin.de...
> hi John,
> "John Carnahan" <carnahan_j@msn.com> ha scritto nel messaggio
> news:%23gmrDkEYEHA.2344@TK2MSFTNGP11.phx.gbl...
> > Wouldn't 6/1/2004
> > 4:00 PM is LESS THAN 6/1/2004 at midnight?
> >
> > ....anyway
> > the "accessdate" field is DATETIME not short
> > I tried the following...
> >
> > select * from visitorhead where accessdate >= '20040525' and accessdate
> <=
> > '20040614' order by accessdate
> >
> > ....result
> > ACCESSDATE
> > 5/25/2004 12:18:25 PM
> > 5/31/2004 3:38:14 PM
> > 6/12/2004 3:40:31 PM
> > 6/13/2004 1:59:06 PM
> >
> > didn't pick these up...
> > 6/14/2004 4:25:46 AM
> > 6/14/2004 11:06:57 AM
> > 6/14/2004 11:26:02 PM
> >
> > Formating the date string to '20040525' rather than '5/25/2004' does
solve
> > the first critereia, but not the second.
>
> as Aaron already pointed out DATETIME type include ttime information too,
so
> 6/1/2004 4:00 PM is LESS THAN 6/1/2004 23:59
> you query must include data till '20040614 23:59....' , so, for short, it
> must be less then '20040615', in order to include all rows from 20040614
> 00:00:001 to 20040614 23:59....
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
> DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> --------- remove DMO to reply
>

> Wouldn't 6/1/2004
> 4:00 PM is LESS THAN 6/1/2004 at midnight?
>
> ....anyway
> the "accessdate" field is DATETIME not short
> I tried the following...
>
> select * from visitorhead where accessdate >= '20040525' and accessdate
<=
> '20040614' order by accessdate
>
> ....result
> ACCESSDATE
> 5/25/2004 12:18:25 PM
> 5/31/2004 3:38:14 PM
> 6/12/2004 3:40:31 PM
> 6/13/2004 1:59:06 PM
>
> didn't pick these up...
> 6/14/2004 4:25:46 AM
> 6/14/2004 11:06:57 AM
> 6/14/2004 11:26:02 PM
>
> Formating the date string to '20040525' rather than '5/25/2004' does solve
> the first critereia, but not the second.