When selecting data with DATE_FORMAT() is not getting proper values MySQL

user_14123491 0
Viewed 107

I'm having a table structure as below.

create table date_test (dt datetime);

insert statements :

insert into date_test values('2019-08-12');
insert into date_test values('2019-09-10');
insert into date_test values('2019-10-04');
insert into date_test values('2019-11-05');
insert into date_test values('2019-12-15');
insert into date_test values('2020-01-15');
insert into date_test values('2020-02-07');
insert into date_test values('2020-03-08');
insert into date_test values('2020-04-10');
insert into date_test values('2020-05-15');
insert into date_test values('2020-06-10');
insert into date_test values('2020-07-15');
insert into date_test values('2020-08-10');
insert into date_test values('2020-09-15');

when i select with below query I'm not getting the proper out.

SELECT * from date_test 
WHERE DATE_FORMAT(dt,'%d-%m-%Y') < DATE_FORMAT(SYSDATE()-INTERVAL 90 DAY,'%d-%m-%Y')

Output :

2019-08-12 00:00:00
2019-09-10 00:00:00
2019-10-04 00:00:00
2019-11-05 00:00:00
2019-12-15 00:00:00
2020-01-15 00:00:00
2020-02-07 00:00:00
2020-03-08 00:00:00
2020-04-10 00:00:00
2020-05-15 00:00:00
2020-06-10 00:00:00
2020-07-15 00:00:00
2020-08-10 00:00:00
2020-09-15 00:00:00

I need a data where dt values are less than 90 Days. Can anyone suggest where to make changes.

where-clause datetime mysql sql string
2 Answers

You are overcomplicating things here I think. Basically you are turning the dates to strings in format dd-mm-yyyy, and the comparing the strings. This does not do what you want. Typically, string 31-12-2020 is greater than 01-01-2021, (because the former starts with 3, and the latter with 0), while the corresponding dates compare the other way around.

You have a datetime column, so just use arithmetics:

where dt < current_date() - interval 90 day

On top of being correct, this is also much more efficient than performing conversions on the column being compared (eg: it may take advantage of an index on dt).

user_106767160

You should not use format in where statement. Use it in select section for format returned result like:

SELECT DATE_FORMAT(dt,'%d-%m-%Y') AS dt
FROM date_test 
WHERE dt < DATE_SUB(SYSDATE(), INTERVAL 90 DAY);

Look it in SQLize.online

user_33993560
Related