applying Date range on string dates.

[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:

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.

 

[cdog]

Have you tried cast() function to convert your strings to datetime values

 

[sbutt]

on doing so:

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_name LIKE '%' and  integra_msg_monitor.msg_date 
BETWEEN  convert(datetime, 'Oct 23 2012 11:01AM', 100) AND convert(datetime, 'Oct 23 2012 11:01AM', 100) and 
integra_msg_monitor.msg_time LIKE '%' ORDER BY integra_msg_monitor.msg_date DESC,integra_msg_monitor.msg_time DESC

I'm getting sql syntax error by the way i'm on mysql 5.x.

 

[skypanther]

I'd swear I have done this sort of thing in PHP-based pages. Something like:

$query = "SELECT * FROM table
WHERE date > '".$lower_date."'
AND date < '".$upper_date."'";

Where $lower_date/$upper_date are a datestring to match the format stored in your database (perhaps created with strftime() or similar).

Tim