0

I want to fetch record where start_date is 01/01/2017 and to_date is 02/25/2018 problem is it cannot show id 2 record

date store in varchar MM/DD/YY

This is my query:

SELECT * 
FROM `new_item` 
WHERE  `from_date` >= '01/01/2017'
  AND `to_date` <= '02/25/2018'

id 2 record not shown

  id   from_date     to_date
   1   01/01/2017    02/05/2018
   2   01/20/2017    06/20/2017
   3   02/05/2018    02/25/2018
   4   02/02/2018    02/20/2018
  • Just the ID 2 you want to hide? – Krismorte Feb 2 '18 at 20:20
  • i think this query shows all the record but it can show 3 records – UNknow Feb 2 '18 at 20:23
  • Why isn't id 2 valid? both dates are between the from and to dates. – Anthony Genovese Feb 2 '18 at 20:26
  • this is my problem @ypercube tells me the problem is in my date format because date store in varchar – UNknow Feb 2 '18 at 20:28
  • Yes, and '02' < '06' (compared as texts. So '02/25/2018' < '06/20/2017' (as texts). Because they are texts for the database. You told the database - when you created the table - that they are VARCHAR, which means text. – ypercubeᵀᴹ Feb 2 '18 at 20:33
2

The problem:

date store in varchar MM/DD/YY

That is your problem. You are storing dates in character columns. Even worse, in a non-aligned format.

Solution:

Use a type that suits your data. You are storing dates, use the DATE type.

Then you won't have to decide or worry about the internal format that dates are stored. You'll only have to provide date literals in a format that MySQL accepts (eg. ISO: YYYY-MM-DD). Your query would then work just fine:

SELECT * 
FROM new_item 
WHERE from_date >= '2017-01-01'
  AND   to_date <= '2018-02-25' ;
  • its mean my query is correct only problem is in my format – UNknow Feb 2 '18 at 20:24
  • The problem is you used VARCHAR type for the date columns. – ypercubeᵀᴹ Feb 2 '18 at 20:25
  • Great job its work – UNknow Feb 2 '18 at 20:40
  • DATE_FORMAT(...) to format dates when reading. – Rick James Feb 10 '18 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.