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

[Karen Hoofnagle]

Are you suggesting the code should look like this:

    $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. "  
00:00:00"] }
         }
);

I still get the same error (pasted again below).

ERROR: DBIx::Class::ResultSet::all(): DBIx::Class::ResultSet::all():  
[SQL::Abstract::_where_field_BETWEEN] Fatal: special op 'between'  
requires an arrayref of two values (or a scalarref or arrayrefref for  
literal SQL) at /home/khoofnag/svn/RCN-TEADWeb/script/../lib/RCN/ 
TEADWeb/Controller/WorkOrder.pm line 93


On Sep 21, 2009, at 12:47 PM, Will Hawes wrote:

> 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(...)" ] }
>
> _______________________________________________
> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
> IRC: irc.perl.org#dbix-class
> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
> Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk