CODEWITHSUNDEEP
mysql
KMS
KMS

Reputation: 574

Mysql query between not working

I am using php and mysql. I have a table with data like below,

Id  Name  SDate
1   AS    12-11-2017
2   SA    11-11-2017
3   Sw    10-11-2017

and datatype of each columns are,

Id int
Name Varchar
SDate Varchar

I'm selecting data between 10-11-2017 to 11-11-2017 like below,

select * from testtable where sdate between '11-11-2017' and '12-11-2017'

Result is empty

But when i select like below,

 select * from testtable where sdate='11-11-2017';

Result is coming

I can't change SDate column to date because 10 million records are there

Upvotes: 0

Views: 87

Answers (3)

Jarek Jóźwik
Jarek Jóźwik

Reputation: 77

You can use STR_TO_DATE
For example, your query could look like:
select * from testtable where STR_TO_DATE(sdate, '%d-%m-%Y') between STR_TO_DATE('11-11-2017', '%d-%m-%Y') and STR_TO_DATE('12-11-2017', '%d-%m-%Y');
But you need to check that, I wrote from memory.

Upvotes: 1

Bernz
Bernz

Reputation: 181

As stated by Faytraneozter, you should never use VARCHAR as a replacement for DATE columns. Varchar is obviously seen as a string, so using >=, <= or BETWEEN will result to doing string comparaison, which is not what you want.

If VARCHAR is absolutely mandatory, you should use a 'YYYYMMDD' format, which at least will give you some leeway to compare values. (in your case, it's unclear if your format is MM-DD-YYYY or DD-MM-YYYY...)

Otherwise, you should consider creating a VIEW from this table. The syntax would look like:

CREATE VIEW mynewview AS
SELECT id, name, str_to_date(`date`, '%d-%m-%Y') AS date2 FROM mytable;

Then, once your view is created, use it normally as:

SELECT * FROM mynewview WHERE date2 BETWEEN '2017-11-11' AND '2017-11-12';

Upvotes: 0

Scoots
Scoots

Reputation: 3102

I'll preface the answer by saying you absolutley should alter the data type on the table. This answer I am about to provide you with will work, but will be horrendously inefficient as it needs to convert every single record in the table every time you run this query.

Anyway, there is a function in mysql called str_to_date (docs). With that, you can convert a varchar to a date at query time, so the following query should suffice:

select
    *
from
    testtable
where
    str_to_date(sdate, '%d-%m-%Y') between str_to_date('11-11-2017', '%d-%m-%Y') and str_to_date('12-11-2017', '%d-%m-%Y')

Upvotes: 1

Related Questions