CODEWITHSUNDEEP
phpmysql
Kallol Das
Kallol Das

Reputation: 13

How to query date in mySQL with string data type

I am trying to get data from mySQL table within two dates. The data type of "date" column is string. My mySQL query is 

SELECT * FROM `accounts` WHERE date between STR_TO_DATE('01/03/2017', '%d/%m/%Y') and STR_TO_DATE('20/03/2017', '%d/%m/%Y')

It shows nothing. if I write, 

SELECT * FROM `accounts` WHERE date between '01/03/2017'  and '20/03/2017'

it takes almost all data. Here is the screen shot.

Screen

Upvotes: 0

Views: 987

Answers (1)

Luci
Luci

Reputation: 1396

If your 'date' data type is string then your query will use string comparison. You have to convert 'date' column to date data type too, STR_TO_DATE(date, '%d/%m/%Y'), if you want date comparison.

You could use something like this

SELECT * FROM `accounts` 
    WHERE STR_TO_DATE(date, '%d/%m/%Y') BETWEEN 
         STR_TO_DATE('01/03/2017', '%d/%m/%Y') AND
         STR_TO_DATE('20/03/2017', '%d/%m/%Y')

Upvotes: 1

Related Questions