Comparing dates

[ShaolinF]

Hi Guys

 

I am trying to get all records that are less than the current date. See code below followed by problem:

 

CREATE TABLE courses (
course_id integer auto_increment NOT NULL,
course_finish_date varchar(10),
PRIMARY KEY(course_id)
);
SELECT * FROM courses WHERE course_finish_date < CURDATE();

 

The SELECT statement works but it doesnt return any results (when it should). I suspect the reason for this is because course_finish_date is a varchar and not a date/timestamp. The column stores the date in the following format: DD/MM/YYYY - Is there any way I can convert it to a date and THEN compare ?

[fenway]

Yup... add  a new DATE column, convert using STR_TO_DATE(), then drop the old one, and then mysql date math is a joke.

[ShaolinF]

Thanks. I couldnt use the STR_TO_DATE() but I managed to use strtotime() to convert the date in a 10 digit date number. Now, I need to compare that date number will the current date to workout the difference between the two. I tried DATEDIFF(course_finish_date, CURDATE()) < 0 - Namely all dates that come BEFORE today, but it returns no results when it should. Any ideas where Im going wrong ?

[Mchl]

Now you can use FROM_UNIXTIME to convert it to DATE (or DATETIME) format. Then just compare it with CURDATE()