Date Comparison in MYSQL format d-m-Y-CodePudding

Good Day!

My date in MYSQL is in d-m-Y format but column is of vachar datatype. When I try below query, it returns correct values

select * from inspection where date=date_format(str_to_date('02-10-2022', '%d-%m-%Y'), '%d-%m-%Y');

But when I compare two dates, it returns wrong values like all dates of all months it compares i.e. 02-09-2022, 02-10-2022 even if I need only for 02-10-2022 etc

select * from inspection where date between date_format(str_to_date('01-10-2022', '%d-%m-%Y'), '%d-%m-%Y') and date_format(str_to_date('02-10-2022', '%d-%m-%Y'), '%d-%m-%Y');

I basically want to search data between two dates, which are of varchar data type but are stored in column as d-m-Y Please guide

Thanks

CodePudding user response：

The result of

date_format(str_to_date('02-10-2022', '%d-%m-%Y'), '%d-%m-%Y')

is '02-10-2022'. str_to_date() is converting your string to a propper date. And then date_format() converts the resulting date to the original string. At the end this piece of code is doing nothing, but is doing it in a quite complex way. If you need an equality comparison you can just use

where date = '02-10-2022'

But when you need a range check, you will need to convert the column to a sortable format - The default is YYYY-MM-DD or '%Y-%m-%d'. Here you can use str_to_date():

select *
from inspection
where str_to_date(date, '%d-%m-%Y')
  between str_to_date('01-10-2022', '%d-%m-%Y')
      and str_to_date('02-10-2022', '%d-%m-%Y')

or simpler:

where str_to_date(date, '%d-%m-%Y') between '2022-10-01' and '2022-10-02'

Better yet, fix your data and store dates in a DATE type column. Your question here is reason enough for that. The sooner you do that, the more time you will save in the future. And you will be able to use indexes for range conditions or for sorting. Using str_to_date() you loose that ability.