Formatting Dates With SQL, Take 3
July 12, 2016 Ted Holt
|This story contains code, which you can download here.
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:
Here’s an example:
SELECT some_date, fmtdate(some_date,'mdy/','iso-') FROM mytable
Notice that FMTDATE can handle internal blanks. This was not intentional; it just worked out that way. It also ignores leading and trailing blanks.
Notice also that FMTDATE does not remove editing characters other than the one specified in the format.
Thanks to Barry for suggesting this improvement. This would be a pitiful world if I had to think of everything myself.
Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.