How to convert CYMD AS400 standard date format to MDY in PHP?

Question

I make quite a few requests in PHP. I output information from these queries, but I would like to change the date format to something that people except programmers can easily read. Currently, I managed to get through using the following:

$compdt = str_split($fin47['COMPDT']); $compdt = "$compdt[3]$compdt[4]/$compdt[5]$compdt[6]/$compdt[1]$compdt[2]" 

The above works great. For example, from the database, I return this for the date:

 1090225 

After doing the array and string binding, I get the following:

 02/25/09 

But when I have a date before 2000, returned from the database as follows:

 960614 

I get this after my line layout:

 61/4/60 

This is obviously not true, because the number of the century is not there, and also zero for the year.

I just launched a few dates that were before 2000 in a year and all formatting is disabled. Is there any simple way to flip the date around or do I have to have a second array structure for dates prior to 2000?

Answer 1

CYMD Date has the format century, year, month, day, cyymmdd, where c is 0 during 1928 to 1999 and is 1 year from 2000 to 2071.

Just did it for you: http://ideone.com/6MQmWk

 <?php function cymdToTime($d) { $matches = null; preg_match('/^(\\d*)?(\\d\\d)(\\d\\d)(\\d\\d)$/', $d, $matches); return strtotime( (($matches[1] + 19) * 100 + $matches[2]) . '-' . $matches[3] . '-' . $matches[4]); } echo strftime('%B %d, %Y', cymdToTime(960614)); // June 14, 1996 echo strftime('%B %d, %Y', cymdToTime(1090225)); // February 25, 2009 

Answer 2

Simple UDF can be used to convert the CYMD format to SQL DATE on the host:

 CREATE FUNCTION QGPL.CYMDTODATE(IN DEC(7)) RETURNS DATE LANGUAGE SQL DETERMINISTIC RETURNS NULL ON NULL INPUT BEGIN RETURN(DATE( SUBSTR(DIGITS(19000000+IN),2,4) || '-' || SUBSTR(DIGITS(IN),4,2) || '-' || SUBSTR(DIGITS(IN),6,2))); END 

It can be used as follows:

 SELECT QGPL.CYMDTODATE(COMPDT) COMPDT FROM MYLIB.MYTABLE