Re: Syntax confusion

[Paul DuBois]

At 9:05 +1000 3/6/03, Jeff wrote:
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?
Not necessarily, but if you want to match all times within a given
date, you'll need to do something like:
BA_DATE >= '2003-02-25 00:00:00' AND BA_DATE <= '2003-02-25 23:59:59'

or

TO_DAYS(FROM_DAYS(BA_DATE)) = '2003-02-25'

The TO_DAYS(FROM_DAYS()) transform chops off the time part.

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