|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
where datefld =... vs datefld Between ... not getting same recorIf I use Where datefld = '1/24/06' my query returns 1 record containing that date. But if I use datefld Between '1/24/06' and '1/27/06' it returns 6 records of which 3 are from '1/24/06'. Why do I not get the 3 records when datefld = '1/24/06'? Here are my queries are resultsets 1st query select recordID, listno, firstdate from subdetail where firstdate = '1/24/06' and listno = 'CC' Returns 51683 CC 2006-01-24 00:00:00.000 2nd query select recordID, listno, firstdate from subdetail where firstdate between '1/24/06' and '1/27/06' and listno = 'CC' returns 18094 CC 2006-01-24 10:50:00.000 <<<--- 40343 CC 2006-01-25 11:58:00.000 51683 CC 2006-01-24 00:00:00.000 <<<--- 51684 CC 2006-01-24 13:07:00.000 51685 CC 2006-01-24 16:39:00.000 <<<--- 51705 CC 2006-01-25 04:50:00.000 why doesn't query 1 get these 3 records with firstdate = '1/24/06'? In resultset1 I see that the time appears to be exactly midnight. But in resultset2 that same record is there along with the other 2 records where the time is greater than midnight. How can I pick these records up also with firstdate = '1/24/06'? Actually, I notice there were 4 records for 1/24/06. Anyway, I tried one
other derivation of this query: select recordID, listno, firstdate from subdetail where firstdate > '1/23/06' and firstdate < '1/25/06' and listno = 'CC' Returns 10236 CC 2006-01-23 13:42:00.000 18094 CC 2006-01-24 10:50:00.000 51683 CC 2006-01-24 00:00:00.000 51684 CC 2006-01-24 13:07:00.000 51685 CC 2006-01-24 16:39:00.000 But this resultset contains a record from 1/23/06. Why is it getting that record with this criteria: firstdate > '1/23/06' and firstdate < '1/25/06' Show quote "Rich" wrote: > Hello > > If I use Where datefld = '1/24/06' my query returns 1 record containing that > date. But if I use datefld Between '1/24/06' and '1/27/06' it returns 6 > records of which 3 are from '1/24/06'. Why do I not get the 3 records when > datefld = '1/24/06'? > > Here are my queries are resultsets > > 1st query > select recordID, listno, firstdate from subdetail > where firstdate = '1/24/06' and listno = 'CC' > > Returns > 51683 CC 2006-01-24 00:00:00.000 > > > 2nd query > select recordID, listno, firstdate from subdetail > where firstdate between '1/24/06' and '1/27/06' and listno = 'CC' > > returns > 18094 CC 2006-01-24 10:50:00.000 <<<--- > 40343 CC 2006-01-25 11:58:00.000 > 51683 CC 2006-01-24 00:00:00.000 <<<--- > 51684 CC 2006-01-24 13:07:00.000 > 51685 CC 2006-01-24 16:39:00.000 <<<--- > 51705 CC 2006-01-25 04:50:00.000 > > why doesn't query 1 get these 3 records with firstdate = '1/24/06'? In > resultset1 I see that the time appears to be exactly midnight. But in > resultset2 that same record is there along with the other 2 records where > the time is greater than midnight. How can I pick these records up also with > firstdate = '1/24/06'? > >
Show quote
"Rich" <R***@discussions.microsoft.com> wrote in message 1/23/06 will equate to: 1/23/06 00:00:00.000news:1D27917D-1D9C-4198-9A8D-AA0620E09BB5@microsoft.com... > Actually, I notice there were 4 records for 1/24/06. Anyway, I tried one > other derivation of this query: > > select recordID, listno, firstdate from subdetail > where firstdate > '1/23/06' and firstdate < '1/25/06' > and listno = 'CC' > > Returns > 10236 CC 2006-01-23 13:42:00.000 > 18094 CC 2006-01-24 10:50:00.000 > 51683 CC 2006-01-24 00:00:00.000 > 51684 CC 2006-01-24 13:07:00.000 > 51685 CC 2006-01-24 16:39:00.000 > > But this resultset contains a record from 1/23/06. Why is it getting that > record with this criteria: > > firstdate > '1/23/06' and firstdate < '1/25/06' So anything greater than that date and TIME stamp will be returned. Rick Sawtell MCT, MCSD, MCDBA Got it. I guess using DateDiff(dd....) only compares the dates and not the
times. So I will use that in my criteria. Show quote "Rick Sawtell" wrote: > > "Rich" <R***@discussions.microsoft.com> wrote in message > news:1D27917D-1D9C-4198-9A8D-AA0620E09BB5@microsoft.com... > > Actually, I notice there were 4 records for 1/24/06. Anyway, I tried one > > other derivation of this query: > > > > select recordID, listno, firstdate from subdetail > > where firstdate > '1/23/06' and firstdate < '1/25/06' > > and listno = 'CC' > > > > Returns > > 10236 CC 2006-01-23 13:42:00.000 > > 18094 CC 2006-01-24 10:50:00.000 > > 51683 CC 2006-01-24 00:00:00.000 > > 51684 CC 2006-01-24 13:07:00.000 > > 51685 CC 2006-01-24 16:39:00.000 > > > > But this resultset contains a record from 1/23/06. Why is it getting that > > record with this criteria: > > > > firstdate > '1/23/06' and firstdate < '1/25/06' > > 1/23/06 will equate to: 1/23/06 00:00:00.000 > > So anything greater than that date and TIME stamp will be returned. > > > Rick Sawtell > MCT, MCSD, MCDBA > > > > To see why you are getting the results you are do
SELECT CAST('1/24/06' AS datetime) When compare a datetime field to a string, SQL implicitly converts the string to a datetime. If you specify only the date, and not the time, in the string, SQL will convert it to a datetime value of that date at midnight. If you want to find all the results that occured on a specific day,you could do select recordID, listno, firstdate from subdetail where DATEDIFF(dd,firstdate,'1/24/06') = 0 and listno = 'CC' -- Show quote"Rich" wrote: > Hello > > If I use Where datefld = '1/24/06' my query returns 1 record containing that > date. But if I use datefld Between '1/24/06' and '1/27/06' it returns 6 > records of which 3 are from '1/24/06'. Why do I not get the 3 records when > datefld = '1/24/06'? > > Here are my queries are resultsets > > 1st query > select recordID, listno, firstdate from subdetail > where firstdate = '1/24/06' and listno = 'CC' > > Returns > 51683 CC 2006-01-24 00:00:00.000 > > > 2nd query > select recordID, listno, firstdate from subdetail > where firstdate between '1/24/06' and '1/27/06' and listno = 'CC' > > returns > 18094 CC 2006-01-24 10:50:00.000 <<<--- > 40343 CC 2006-01-25 11:58:00.000 > 51683 CC 2006-01-24 00:00:00.000 <<<--- > 51684 CC 2006-01-24 13:07:00.000 > 51685 CC 2006-01-24 16:39:00.000 <<<--- > 51705 CC 2006-01-25 04:50:00.000 > > why doesn't query 1 get these 3 records with firstdate = '1/24/06'? In > resultset1 I see that the time appears to be exactly midnight. But in > resultset2 that same record is there along with the other 2 records where > the time is greater than midnight. How can I pick these records up also with > firstdate = '1/24/06'? > > Thank you. DateDiff(dd...) is giving me more consistent results.
Much appreciated. Show quote "Mark Williams" wrote: > To see why you are getting the results you are do > > SELECT CAST('1/24/06' AS datetime) > > When compare a datetime field to a string, SQL implicitly converts the > string to a datetime. If you specify only the date, and not the time, in the > string, SQL will convert it to a datetime value of that date at midnight. > > If you want to find all the results that occured on a specific day,you could > do > > > select recordID, listno, firstdate from subdetail > where DATEDIFF(dd,firstdate,'1/24/06') = 0 and listno = 'CC' > > > -- > > "Rich" wrote: > > > Hello > > > > If I use Where datefld = '1/24/06' my query returns 1 record containing that > > date. But if I use datefld Between '1/24/06' and '1/27/06' it returns 6 > > records of which 3 are from '1/24/06'. Why do I not get the 3 records when > > datefld = '1/24/06'? > > > > Here are my queries are resultsets > > > > 1st query > > select recordID, listno, firstdate from subdetail > > where firstdate = '1/24/06' and listno = 'CC' > > > > Returns > > 51683 CC 2006-01-24 00:00:00.000 > > > > > > 2nd query > > select recordID, listno, firstdate from subdetail > > where firstdate between '1/24/06' and '1/27/06' and listno = 'CC' > > > > returns > > 18094 CC 2006-01-24 10:50:00.000 <<<--- > > 40343 CC 2006-01-25 11:58:00.000 > > 51683 CC 2006-01-24 00:00:00.000 <<<--- > > 51684 CC 2006-01-24 13:07:00.000 > > 51685 CC 2006-01-24 16:39:00.000 <<<--- > > 51705 CC 2006-01-25 04:50:00.000 > > > > why doesn't query 1 get these 3 records with firstdate = '1/24/06'? In > > resultset1 I see that the time appears to be exactly midnight. But in > > resultset2 that same record is there along with the other 2 records where > > the time is greater than midnight. How can I pick these records up also with > > firstdate = '1/24/06'? > > > > Please see:
http://www.aspfaq.com/2280 http://www.aspfaq.com/2312 http://www.karaszi.com/SQLServer/info_datetime.asp In short, you should: (a) use an unambiguous date format, such as YYYYMMDD. (b) use range queries. If you have an index, datecolumn >= 'YYYYMMDD' and datecolumn < 'YYYYMMDD' is going to work a lot faster than non-sargable things like DATEDIFF. Show quote "Rich" <R***@discussions.microsoft.com> wrote in message news:B2810AED-21E1-4622-8089-142AF813E94F@microsoft.com... > Hello > > If I use Where datefld = '1/24/06' my query returns 1 record containing > that > date. But if I use datefld Between '1/24/06' and '1/27/06' it returns 6 > records of which 3 are from '1/24/06'. Why do I not get the 3 records > when > datefld = '1/24/06'? > > Here are my queries are resultsets > > 1st query > select recordID, listno, firstdate from subdetail > where firstdate = '1/24/06' and listno = 'CC' > > Returns > 51683 CC 2006-01-24 00:00:00.000 > > > 2nd query > select recordID, listno, firstdate from subdetail > where firstdate between '1/24/06' and '1/27/06' and listno = 'CC' > > returns > 18094 CC 2006-01-24 10:50:00.000 <<<--- > 40343 CC 2006-01-25 11:58:00.000 > 51683 CC 2006-01-24 00:00:00.000 <<<--- > 51684 CC 2006-01-24 13:07:00.000 > 51685 CC 2006-01-24 16:39:00.000 <<<--- > 51705 CC 2006-01-25 04:50:00.000 > > why doesn't query 1 get these 3 records with firstdate = '1/24/06'? In > resultset1 I see that the time appears to be exactly midnight. But in > resultset2 that same record is there along with the other 2 records where > the time is greater than midnight. How can I pick these records up also > with > firstdate = '1/24/06'? > >  |
|||||||||||||||||||||||
Other interesting topics