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.
Advertisement
Answer
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
).