• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Formatting Dates with SQL, Take 2

    May 5, 2015 Hey, Ted

    I am trying to use your FMTDATE SQL function but the system tells me it can’t find it. I know FMTDATE is there. Can you help?

    –Robert

    Note: The code accompanying this article is available for download here.

    I’ve received a compliment or two (at least) for the FMTDATE (format date) function I published back in 2009. (Can it really have been so long? How time flies!) I use it heavily, and I’m not the only one. If you’re not familiar with FMTDATE, please read Formatting Dates with SQL before continuing.

    To address Robert’s question, the reason the system said that it could not find the function was that Robert was passing a character argument to the first parameter. I wrote the FMTDATE function to accept a numeric value in the first parameter. One might think that the system would respond that the first parameter was of the wrong data type, but not so. Here’s why: SQL allows two or more functions to have the same name as long as the parameter lists differ.

    This phenomenon is known as function overloading, and it’s marvelous. When the system must run a function, it looks for a function of the correct name that has a compatible parameter list. “Compatible” means:

    1. The number of parameters must equal the number of supplied arguments

    2. The system must be able to convert each argument to the data type of the corresponding parameter in the function definition

    To help Robert with his problem was a simple matter of overloading the FMTDATE function. In a matter of minutes, I made two changes of significance to my RPG source code.

    1. I renamed the FMTDATE subprocedure to FMTDATEN. This was not necessary. I added the N to emphasize that the subprocedure was for numeric data. (I like clarity.)

    2. I added a subprocedure called FMTDATEA, where A stands for alphanumeric. FMTDATE converts the character value to numeric and calls FMTDATEN. Here’s that subprocedure in its entirety.

    P FmtDateA        b                   export
    D                 pi            10a   varying
    D   inDate                       8a   varying const
    D   inFromFmt                    8a   varying const
    D   inToFmt                      8a   varying const 
    D   inNullArray                  6a   const
    D   ouNull                       5i 0
    
     /free
         monitor;
            return FmtDateN (%dec(inDate:8:0): inFromFmt:
                             inToFmt: inNullArray: ouNull);
         on-error;
            ouNull = gNullValue;
            return *blank;
         endmon;
    
     /end-free
    P                 e
    

    After creating the module and service program, I created two (yes, two!) FMTDATE functions.

    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(FMTDATEN)'
    
    create function xxx/fmtdate
    (inDate varchar(8), 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(FMTDATEA)'
    

    Two functions of the same name run two different subprocedures in the FMTDATE service program. Is that fine, or what?

    One word of warning: If you’re using FMTDATE within programs, as I do, you will have to recompile those programs because of the change to the procedure exports in the service program.

    Now, if only IBM would allow overloading of subprocedures in RPG! I could do some powerful if-less programming!

    RELATED STORIES

    Formatting Dates with SQL

    UDF Concepts (IBM Knowledge Center)

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Remain Software:  Take control of your software modernization process with TD/OMS and X-Analysis
    ProData Computer Services:  SQL/Pro 5.0 - New & Improved! Download today!
    United Computer Group:  VAULT400 BaaS delivers secure cloud backup and DR solutions

    An Open Letter To IBM From A Developer Technology Refresh Highlighted By Development Languages And Native Flash Storage

    Leave a Reply Cancel reply

Volume 15, Number 09 -- May 5, 2015
THIS ISSUE SPONSORED BY:

WorksRight Software
PowerTech
United Computer Group, Inc.

Table of Contents

  • Pipelined User-Defined Table Functions In DB2 For i
  • Formatting Dates with SQL, Take 2
  • Ruby And Existing Databases

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle