List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 5 2003 11:12pm
Subject:Re: Syntax confusion
View as plain text  
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" <paul@stripped>
>To: "Jeff" <MySQL@stripped>; "MySQL List" <mysql@stripped>
>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

Thread
Syntax confusionJeff5 Mar
  • Re: Syntax confusionPaul DuBois5 Mar
  • Re: Syntax confusionJeff6 Mar
    • Re: Syntax confusionPaul DuBois6 Mar
      • Re: Syntax confusionBenjamin Pflugmann6 Mar
        • Re: Syntax confusionPaul DuBois6 Mar
  • Re: Syntax confusionJeff6 Mar