List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 5 2003 10:51pm
Subject:Re: Syntax confusion
View as plain text  
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