tsvika_t has asked for the wisdom of the Perl Monks concerning the following question:

Hi, How do I compare a string date like: 26/11/1987 to a date column in informix data base? Thanks Tsvika.

Replies are listed 'Best First'.
Re: compare dates
by davorg (Chancellor) on Jan 14, 2001 at 16:32 UTC

    Best way to compare dates is probably to format them like YYYYMMDD. That way you can compare them numerically.

    You can convert your date into this format like this:

    $std_date = sprintf('%04d%02d%02d', (split m|/|, $date)[2, 1, 0]);

    And I'm betting there's an Informix function that returns dates in a very similar format.

    --
    <http://www.dave.org.uk>

    "Perl makes the fun jobs fun
    and the boring jobs bearable" - me

      I just wanted to point out that YYYYMMDD is also safe if you goof and compare it string-wise. That also makes it a great format for mixed data like "Mark20010106" since you can do the whole sort without an unpack/split/regex pass or heavy-duty transforming. Sexy...

      --
      $you = new YOU;
      honk() if $you->love(perl)

Re: compare dates
by chipmunk (Parson) on Jan 14, 2001 at 19:57 UTC
    Do you want to compare your dates in Perl or in SQL? You've received some good answers for comparing your dates in Perl. If you have a need to compare them in SQL, you would use Informix's string-to-date conversion and then compare the dates with the basic comparison operators.

    I don't have experience with Informix; here's how it would look with DBI and Oracle, using TO_DATE to convert the string. This example looks for rows with a date_new column before 26/11/1987.

    my $sth = $dbh->prepare(<<EndOfSQL); SELECT id FROM mytable WHERE date_new < TO_DATE(?, 'DD/MM/YYYY') EndOfSQL $sth->execute('26/11/1987'); while (my @row = $sth->fetchrow_array()) { print "@row\n"; }
    I hope this is helpful; I would expect an Informix solution to look remarkably similar.
      I ++'ed this because he is absolutely right, about 90% of the time, you should be doing your date compares in SQL. Data in a DB should be tested in a DB whereever possible just to lower the amount of network traffic you impose on the system.

      --
      $you = new YOU;
      honk() if $you->love(perl)

Re: compare dates
by mrmick (Curate) on Jan 14, 2001 at 17:26 UTC
    You could also use Date::Calc , Date::format , Date::Manip, and Date::Parse if you want to do a little more with your date values than simply comparing them as strings.

    Mick