[Dbix-class] Writing a query with a BETWEEN in the WHERE clause and the to_date(function)

[Will Hawes]

2009/9/21 Karen Hoofnagle <karen at hoofnagle.org>:
> I need to create the following query using DBIx:Class (assume I'm using the
> most recent release of DBIx::Class)
> select * from user_read_log where datetime between to_date(20090101,
> 'YYYYMMDD') and to_date(20091001, 'YYYYMMDD')
> I've read the section of SQL::Abstract dealing with arrayrefs and scalarrefs
> and I am clearly not getting it. I've screwed around with a bunch of
> different syntaxes and am missing the mark.
> This is just one of my attempts to make this thing fly:
>
>   $rs = $c->model('DB::User_Read_Log')->search(
>         {
>          datetime =>{"between", \["to_date(?, 'YYYYMMDD HH24:MM:SS')"
> =>$start. " 00:00:00",
>                                                      "to_date(?, 'YYYYMMDD
> HH24:MM:SS')" => $end. " 23:59:59"]
>          }
>
>         }
>         );

IIRC you need to pass in an arrayref containing two scalarrefs as follows:

datetime => { "between", [ \"to_date(...)", \"to_date(...)" ] }