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

Rotten, huh?

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:

MDY

DMY

YMD

JUL

LONGJUL

CMDY

CDMY

CYMD

ISO, JIS, YYMD

EUR, DMYY

USA,
MDYY

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.

If a date is invalid, FMTDATE returns null.
You may place a leading asterisk on a format parameter, as in RPG, if you like. That is to say, MDY and *MDY are equivalent.
You may specify a separator character at the end of a format, but it will be ignored for the input date.
The ampersand separator character represents a blank.
Unlike RPG, no separator character is implied for ISO, EUR, JIS, and USA dates.
Unlike RPG, you can use any character except for a letter as a separator character.

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.

Omit Commas from Numeric Dates

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot