Yes Paul, that's what I meant. See, even confusing myself now ;-) The field is datetime as the time is used in other functions... Would I be better using 2 separate fields?
Jeff ----- Original Message ----- From: "Paul DuBois" <[EMAIL PROTECTED]> To: "Jeff" <[EMAIL PROTECTED]>; "MySQL List" <[EMAIL PROTECTED]> Sent: Thursday, March 06, 2003 8:51 AM Subject: Re: Syntax confusion > At 8:40 +1000 3/6/03, Jeff wrote: > >Hi, perform the following query > > > >SELECT BA_ID, BA_DATE > >FROM BA > >WHERE SUBSTRING(BA_ID,1,3) = 'TSV' > > > >and get > > > >'TSV12345-00001','2003-02-25 00:00:00' > >'TSV12345-00002','2003-02-25 00:00:00' > >'TSV14818-00003','2003-02-19 00:00:00' > >'TSV14836-00015','2003-02-27 00:00:00' > >'TSV14841-00009','2003-02-28 00:00:00' > > > >which is OK. When I only want data from 2/25/2003 and query with > > > >SELECT BA_ID, BA_DATE > >FROM BA > >WHERE SUBSTRING(BA_ID,1,3) = 'TSV' > >AND BA_DATE = '2003-25-2' > > > >I get 0 rows returned.... > > Not surprising, since there is no month 25! :-) > > Do you mean '2003-02-25'? > > By the way, do all your BA_DATE values have 00:00:00 in the time > part? If so, you may want to convert the column to DATE. If not, > then if you want to match values that occur anywhere is a given > date, a comparison like you're doing won't work. > > > > >The book hinted at using DATE_FORMAT which I did > > > >SELECT BA_ID, BA_DATE > >FROM BA > >WHERE SUBSTRING(BA_ID,1,3) = 'TSV' > >AND BA_DATE = DATE_FORMAT('2003-25-2', '%y-%d-%m') > > > >But still 0 rows... D'OH... What am I missing here? Thanks for any > >help... I would hate to have to tell the boss we're going back to > >VB-ADO-MSAccess ;-) > > > >Jeff > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php