• 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 3

    November 17, 2015 Hey, Ted

    Great technique shared! Function overloading is a real boon to SQL programming. Here’s another way that requires less code and is less invasive (i.e., you won’t have to recompile anything.)

    Leave your original FMTDATE function alone and add the following to the SQL source:

    create function xxx/fmtdate
    (inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8))
    returns varchar(10)
    language SQL
    specific FMTDATEA
    deterministic
    returns null on null input
    
    begin
     declare DateNum numeric (8,0);
     declare OutDate varchar(10);
     set DateNum = decimal(inDate);
     set OutDate = FmtDate(DateNum, inFromFmt, inToFmt);
     return OutDate;
    end
    

    I’ve used this technique to overload many user-defined functions and keep the HLL programming simple.

    –Michael Quigley

    This is an excellent idea, Michael. I intend to overload functions your way from now on.

    Just to make sure everyone understands what’s going on here, I used RPG to define two different subprocedures to carry out two FMTDATE functions. When I added the version that accepts a character argument, I modified the RPG. In the process, I decided to rename the existing FMTDATE subprocedure to FMTDATEN. If I had not renamed that subprocedure, callers would not have had to be recompiled, but I am nit-picky that way.

    Michael’s version requires no RPG changes. Instead, he creates the character version of FMTDATE with SQL only.

    Michael’s method is far superior to the one I published! I am very grateful to him for sharing it with us!

    RELATED STORIES

    Formatting Dates with SQL, Take 2

    Formatting Dates with SQL

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Two Steps Forward, No Steps Back

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    LEARN MORE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    NGS:  Nov. 18 FREE Webinar: Migration Alternatives for Query/400 Users
    United Computer Group:  VAULT400 BaaS delivers secure cloud backup and DR solutions
    BCD:  IBM i Webinar with Jon Paris - RPG OA: So Misunderstood! November 18 at 1pm EST

    HelpSystems’ Application Integration Begins With GUI ERP Upgrades: Love ‘Em or Leave ‘Em?

    Leave a Reply Cancel reply

Volume 15, Number 24 -- November 17, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
Connectria
WorksRight Software

Table of Contents

  • An Introduction to Processing XML with RPG, Part 4
  • Formatting Dates with SQL, Take 3
  • Data Scrubbing Functions In DB2 For i

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