Class::DBI, Class::DBI::AbstractSearch, and Oracle to_date

I'm trying to produce the following SQL using CDBI:

select * from mytable
where ref = "foo"
  and to_date(received_date, 'DD-MM-YYYY') < to_date('01-04-2011', 'DD-MM-YYYY')

The closest syntax I can think of is:

mycdbi->search_where({
    ref           => 'foo',
    received_date => { '<' => ["to_date(?, 'DD-MM-YYYY')", '01-04-2011']  }
});

However that's not converting the column mytable.received_date using Oracle function to_date.

So what's the correct way to do it?

Ps: please don't say use DBIC as the code needs to stay i开发者_Go百科n CDBI.

thanks!

Might be easiest to just use retrieve_from_sql and do it by hand:

my @things = mycdbi->retrieve_from_sql(qq{
        ref = 'foo'
    AND to_date(received_date, 'DD-MM-YYYY') < to_date('01-04-2011', 'DD-MM-YYYY')
});

I usually end up pushing the ORMs out of the way for any instantiation more complicated than my $thing = Thing->retrieve(x), I already know SQL so I don't need to learn yet another API that tries to implement SQL and usually does so rather poorly.