Using YYYYMMDD int to group per month

The place for general PHP questions and hints for PHP on IBM i
Post Reply
Posts: 1
Joined: Thu Sep 24, 2015 12:31 pm

Using YYYYMMDD int to group per month

Post by joos1424 » Thu Sep 24, 2015 12:59 pm


- ZF1
- DB2 on IBM i OS/400 V6R2
- Zend_Db_Table_Abstract

I'm trying to use an YYYYMMDD formatted int(8) to group records per month with Zend_Db_Table_Select.

Code: Select all

$fields = array('JURRSC','JURD#R' => 'CHAR(JURD#R, ISO)' ,'SUM(JURH#D) AS "totale tijd"');
$group = array('JURRSC','JURD#R','JURH#D');
$config = Zend_Registry::get('config');
$select = $this->select()->setIntegrityCheck(false)
  ->from('PJUREG', $fields)
  ->where('JURBDF = ?', $config->bedrijf->nummer)
  ->where('JURD#R >= ?', $begin)
  ->where('JURD#R <= ?', $eind)

return $this->fetchAll($select);
I've tried things like CHAR(field, ISO), cast(field, char(8)), to_char(field, "YYYYMMDD"), to_date(field, "YYYYMMDD"), but this causes all sorts of errors like "Argument 2 at functie CHAR is invalid. SQLCODE=-171"

Help getting data in the right format to group by month would be greatly appreciated.



Post Reply