DD-MM-YYYY date format and mysql

Question

I have a mysql table :

dateStarted     varchar(45)
dateEnded       varchar(45)

Example:

There are varchar (I dont know why Previous programmer used this).

Can I search between two dates in mysql with varchar type?

Thanks in advance

Answer 1

Nope. You have to change format to a proper one.

Previous programmer used this because of ignorance. You have to correct his mistake.

Don't be afraid of some extra work. Actually it's part of every programmer's job. There is no code in the whole world, which is perfect forever. Constant code improving is called refactoring and take big part of every programmer's worktime.

Answer 2

SELECT 
    *
FROM test1
    WHERE STR_TO_DATE(:date, '%d-%m-%Y') BETWEEN 
        STR_TO_DATE(dateStart,'%d-%m-%Y') AND
        STR_TO_DATE(dateEnd,'%d-%m-%Y')

Just tried this on your dataset, and it works AFAICT.

But you should of course heed the advice given by the others here, and try to fix your predecessor's mistakes, if at all possible within time-constraints. This will become an issue at some point, due to the amount of casting going on.

Answer 3

You will need to determine the format that the two fields are in. Are they seconds from UNIX epoch? Are they YYYYMMDD format? Once you've done that, it would be fairly easy to convert it to a date and compare them.