Formatting Dates With SQL, Take 3

I love it when someone improves something I’ve produced. In this case, that someone was Barry Arnold, an A-1COBOL programmer with whom I had the privilege to work once upon a time. Barry improved my FMTDATE SQL function for use in his shop. Maybe his enhancement will help you, too.

Barry’s shop, like many others, must deal with data from many different sources, and that data is often formatted in ways that are not conducive to RPG and COBOL programming. Barry found himself having to de-edit character dates before passing them to the FMTDATE function. He reasoned that FMTDATE should be able to handle the de-editing, and I have to agree.

I modified the character version of FMTDATE in two ways:

• I increased the size of the date (the first argument) from eight to 10 bytes to allow for edited eight-byte dates.
• If an editing character is specified in the source format (the second argument), I remove all occurrences of the editing character from the date before attempting the conversion. In the previous version, I ignored an editing character, as I do with the numeric version of FMTDATE.

Here’s an example:

SELECT some_date, fmtdate(some_date,'mdy/','iso-')
  FROM mytable