Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.
We all know about the use of %CHAR to convert a true Date variable to text. But this built-in function is extremely ridged in its ability to format the resulting character text.
About "a hundred years ago" I began using the ILE CEE APIs for various tasks, including date formatting. If you're one of our COZZI TOOLS customers, you're already using these API within the CVTDATE and EDITDATE functions.
But converting a true date variable to something that matches the user's requirements can be a challenge when they want the date in an accounting format or similar. For example, we are often asked how to use EDITDATE to format the date in MMM YYYY format, as in "Aug 2015" for accounting departments.
With COZZI TOOLS, you would simply include the DATES source member from QCPYSRC and then use EDITDATE
myDateTxt = editDate( myDate : 'Mmm YYYY');
But if you don't have COZZI TOOLS (and you should) then you can use the CEEDAYS and CEEDATE APIs to achieve the same result.
Here's a self-contained code snip that should work on your system just fine:
H DFTACTGRP(*NO) ACTGRP(*NEW) OPTION(*NODEBUGIO:*SRCSTMT) D CEEDAYS PR extProc('CEEDAYS') D OPDESC D inDate 255A Const OPTIONS(*VARSIZE) D inFormat 255A Const OPTIONS(*VARSIZE) D nDays 10I 0 D apiError 12A OPTIONS(*OMIT) D CEEDATE PR extProc('CEEDATE') D OPDESC D inDays 10I 0 D outFormat 255A Const OPTIONS(*VARSIZE) D outDate 255A OPTIONS(*VARSIZE) D apiErrorDS 12A OPTIONS(*OMIT) D lilian S 10I 0 D myDATE S D DATFMT(*ISO) INZ(*SYS) D myDateTxt S 10A C eval *INLR = *ON /free CEEDAYS( %char(myDate:*ISO) : 'YYYY-MM-DD' :lilian : *OMIT); CEEDATE( lilian : 'Mmm YYYY' : myDateTxt : *OMIT); // myDateTxt = 'Aug 2015' return; /end-free
The two CEE APIs convert a text date into the so called Lilian "date" which is really just the number of days since 14 Oct 1582. But we've seen that movie before.
The cool stuff happens in CEEDATE. You pass in the number of days variable, a formatting "Picture" (any old COBOL programmers out there?) and a return variable to receive the date in the format you specified. It works well and very fast.
The number of formatting codes is vast. You have M for months, D for day, and Y for year. Use 2, 3, 10 of them to make digits or words. The complete formatting codes listing is available on IBM.com at this link.
What's happening above, I'm using the Mmm format code, which means, return the Month as a 3-character word/abbreviation in upper/lower case. For example, AUGUST is returned as "Aug". If you prefer all caps, then use MMM instead. To return the day, use the capital DD or if you prefer zero-suppressed days such as 3 instead of 03, then use ZD for the day.
The Year is more obvious. YY for 2-digit years, and YYYY for 4-digit years. The options are nearly endless, so again, review the formatting codes on IBM.com.
The SQL built in functions VARCHAR_FORMAT and TIMESTAMP_FORMAT are also worth becoming familiar with if you are on a current release.
Yes the enhancements to VARCHAR's new VARCHAR_FORMAT ("new" on IBM i anyway) is pretty cool. It also supports editing numeric fields with commas, periods, dollar signs, etc. Pretty nice, although you do have to wrap your resulting values in a SUBSTR() or even CHAR(varchar_format( myNumericValue, '................') , 10) in order to have it not return a 255-byte (or whatever its default length is) result.