applying Date range on string dates.

Discussion in 'Database Support' started by sbutt, Nov 4, 2009.

  1. sbutt

    sbutt Guppy

    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.
  2. cdog

    cdog Perch

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

    sbutt Guppy

    on doing so:

    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_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.
  4. skypanther

    skypanther Exalted Code Master!

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

    PHP:
    $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

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services