Using YYYYMMDD int to group per month

The place for general PHP questions and hints for PHP on IBM i

Using YYYYMMDD int to group per month

Postby joos1424 on Thu Sep 24, 2015 12:59 pm

Hello,

- 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)
  ->group($group)
  ->order($group)
;

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.

Regards,

Joost
joos1424
 
Posts: 1
Joined: Thu Sep 24, 2015 12:31 pm

Return to PHP Questions / Hints

Who is online

Users browsing this forum: Google [Bot] and 1 guest