I'm using Zend_Db to query a table. I have some WHERE clauses that need to be dates and it's not clear (at first glance) how to do this "the right way". Does Zend_Db provide an abstraction so I don't need to concern myself with how the backend (Oracle, MySQL, etc.) expects its dates? Or do I need to format things in the way my backend expects them.

I'm trying the following with an Oracle based database (PO_DATE is a timestamp field)

$table = $this->getDbTable();				
$select = $table->select()->where('1 = ?', 1);		
$select->where('PO_DATE = ?', '2009-12-02');

and I get the following exception

  <b>Message:</b> 1843 ORA-01843: not a valid month SELECT "TABLE_NAME".* FROM "SYSTEM"."TABLE_NAME" WHERE (1 = 1) AND (PO_DATE = *'2009-12-02')  </p>

I realize I could do something like this

$date = new Zend_Db_Expr(
"to_date('2009-12-02', 'YYYY-MM-DD')"
);
$select->where('PO_DATE = ?', $date);

but that ties me to Oracle's date based Expressions, which partially defeats the purpose of using a SQL abstraction layer.

Is there a generic way to do date queries independent of the back-end implementation with Zend_Db?

Accepted Answer

I would definitely like such a capability, however, I haven't found any in the Zend Framework. This issue in their tracker also makes me pretty confident its not in there yet.

Written by Brian Fisher
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki