Date and time format DD-MM-YYYY and MySQL

Question

I have a MySQL table:

dateStarted     varchar(45)
dateEnded       varchar(45)

Example:

alt text http://img8.imageshack.us/img8/765/dated.jpg

There is varchar (I don't know why the previous software engineer used this).

Is it possible to search between two dates in MySQL with type varchar?

Answer 1

Nope. You must change the format to the correct one.

A previous software engineer used this because of ignorance. You must correct your mistake.

Do not be afraid of extra work. This is actually part of every programmer’s work. All over the world there is no code that is perfect forever. Continuous improvement of the code is called refactoringand takes up most of each working time of the development engineer.

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')

, AFAICT.

, , , , , . - - , .

Answer 3

You will need to determine the format in which the two fields are located. Are they seconds from the UNIX era? Are they the format YYYYMMDD? Once you do, it would be easy to convert it to a date and compare them.