[sbutt]

hi folks,
I have a table in mysql 5, which contains some date value (dd.mm.yyyy), but the column type is varchar. Now i want to query certain rows based on a given date range.

My query is the following:

Code:

SELECT integra_msg_monitor.msg_name, integra_msg_monitor.msg_date, integra_msg_monitor.msg_time FROM IntegraDB.integra_msg_monitor integra_msg_monitor where integra_msg_monitor.msg_date  BETWEEN  date_format(str_to_date('23.10.2009', '%d.%m.%Y'), '%d.%m.%Y') AND date_format(str_to_date('27.11.2009', '%d.%m.%Y'), '%d.%m.%Y')

The problem is that not all the correct rows are returned. Few rows are missing, e.g. i have a row with dates like 04.11.2009, which is not returned by this above query.

The problem in my opinion is that the BETWEEN clause is treating the dates as string (as the column type is varchar) and hence not returning the correct rows.

So is there anyway to treat the string column as "datatime" at runtime and do the BETWEEN query in order to get the correct values within the given data range?

Thanks.