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

[Karen Hoofnagle]

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"]
          }

         }
         );


This particular version returns the error

	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


Suggestions both for solving the problem at hand and for reading to  
really understand how to use functions in a where clause when coming  
from DBIx:Class. (Yes, I read the SQL::Abstract cpan page and it  
doesn't really serve someone dropping in conceptually from  
DBIx::Class. I'm looking for other discussions.)
Once I properly understand this stuff, I will see what I can do to add  
this example to the DBIx:Class cookbook  and if I can find a way as a  
n00b to decently articulate what I've not been getting up to now, I'll  
see about blogging that in as well since it seems fundamental to  
getting along effectively with the package.

Thanks,
Karen
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20090921/3fd02ae2/attachment.htm