Formatting Dates with SQL
August 19, 2009 Ted Holt
Note: The code accompanying this article is available for download here.
The famous ERP package with which I work at my day job stores dates in seven-digit packed decimal fields in CYYMMDD format. Reformatting these dates into something readable in an SQL query is challenging, to put it mildly. To complicate matters, our homegrown systems have dates in YYYYMMDD formats, and outfiles of CL commands have dates in MMDDYY format. I finally got tired of converting and substringing and concatenating, and decided to write a general SQL function to convert dates. I hastily threw it together, but it seems to be working fine. Feel free to suggest improvements.
Before we look at my function, let’s look more closely at the problem. Suppose I want to reformat a CYYMMDD packed decimal field named CRDATE into MM/DD/YYYY format. How would I do that, using only the tools SQL provides me? I’d probably do something like this:
select whatever, substr( digits(dec(19000000+crdate,8,0)), 5, 2 ) || '/' || substr( digits(dec(19000000+crdate,8,0)), 7, 2 ) || '/' || substr( digits(dec(19000000+crdate,8,0)), 1, 4 ) from mylib/myfile
I have written about this problem before. My XDATE function is one solution. But I decided I wanted a more generic solution. I wanted a function that would convert a numeric date in any format to any other format. So I wrote the FMTDATE function.
FMTDATE requires three parameters: a numeric value that represents a date; the format in which the numeric date is stored; and the format into which the date is to be converted. The result of the function is a 10-character variable-length value.
The following date formats are supported:
The format ID’s are not case sensitive.
Here’s the previous example, using the FMTDATE function:
select whatever, fmtdate(CRDATE,'CYMD','MDYY/') from mylib/myfile
Here are a few thoughts to keep in mind when using FMTDATE.
To create and use this function, follow these steps, replacing xxx with your library of choice. I suggest you place the service program and SQL function in a generally available library, such as QGPL.
1. Place the source code into the FMTDATE RPG source member. The source code is attached to this article. Use FTP or some other file transfer utility to load it into the source physical file member.
2. Compile the source member into a module.
CRTRPGMOD MODULE(xxx/FMTDATE) SRCFILE(xxx/QRPGLESRC) SRCMBR(FMTDATE)
3. Create a service program from the module.
CRTSRVPGM SRVPGM(xxx/FMTDATE) MODULE(xxx/FMTDATE) EXPORT(*ALL)
4. In SQL, create a function to run the service program.
create function xxx/fmtdate (inDate dec(8,0), inFromFmt varchar(8), inToFmt varchar(8)) returns varchar(10) language rpgle parameter style general with nulls deterministic no sql returns null on null input no external action not fenced no final call allow parallel no scratchpad external name 'XXX/FMTDATE(FMTDATE)'
Have fun. Let me know if you find any bugs or have suggestions for improvement.
Send your comments to Ted via the IT Jungle Contact page.