get record between two dates

2018-02-02 21:24:17

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

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_

  • 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' ;

    2018-02-02 22:54:11