Use SQL functions to create RPG functions.

If you've been using SQL, you've probably noticed that there are many functions built into SQL that RPG doesn't have. We could go to a lot of effort to re-create those in RPG, but it's much easier to just leverage the existing SQL FUNCTIONS. To make this happen, we need to create an RPG module with embedded SQL. That module will contain a series of date-related functions, and we'll compile that module into a service program for use by other RPG programs.

The code for the DATENAME RPG module is as follows:

H NOMAIN                                  

D GetDayNam       PR            20           

D InpDat                          D                                            

D GetMonNam       PR            20               

D InpDat                          D 

D GetDatNam       PR            40               

D InpDat                          D    

P GetDayNam       B                   EXPORT     

D                 PI            20               

D InpDat                          D               

D DayNam          S             20                

C/EXEC SQL                                       

C+ SET :DayNam = DAYNAME(:InpDat)               

C/END-EXEC                                      

C                   RETURN    DayNam            

P                 E                              

P GetMonNam       B                   EXPORT    

D                 PI            20              

D InpDat                          D             

D MonNam          S             20              

C/EXEC SQL                                  

C+  SET :MonNam = MONTHNAME(:InpDat)        

C/END-EXEC                                  

C                   RETURN    MonNam        

P                 E                         

P GetDatNam       B                   EXPORT

D                 PI            40          

D InpDat                          D             

D DatNam          S             40                      

C/EXEC SQL                                       

C+ SET :DatNam = DAYNAME(:InpDat) CONCAT ', ' CONCAT    

C+     MONTHNAME(:InpDat) CONCAT ' ' CONCAT      

C+     DAYOFMONTH(:InpDat) CONCAT ' ' CONCAT   

C+     YEAR(:InpDat)                              

C/END-EXEC                                            

C                   RETURN    DatNam            

P                 E     

This module contains three functions: GetDayNam, GetMonNam, and GetDatNam. They each take in a date field as an argument/parameter and return a character string containing the day of week name, month name, or full date name, respectively.

Compile this source into a module with the following command:

CRTSQLRPGI OBJ(DATENAME) OBJTYPE(*MODULE) DBGVIEW(*SOURCE)

Once the module is compiled, use the following command to create the service program:

CRTSRVPGM SRVPGM(DATENAME) EXPORT(*ALL)

This creates the service program in the *CALLER activation group. Any program that wants to use the date name functions must simply include the service program in its binding directory or explicitly list it at compile time. The following compile statement shows how to reference a binding directory:

CRTBNDRPG PGM(MYTEST) DFTACTGRP(*NO) BNDDIR(MYBNDDIR)

The test program might contain the following code:

D DATE            S               D   

D DAYNAM          S             20    

D MONNAM          S             20    

D DATENAM         S             40    

D GetDayNam       PR            20    

D InpDat                          D   

D GetMonNam       PR            20    

D InpDat                          D   

D GetDatNam       PR            40    

D InpDat                          D   

 /FREE                                

    Date = %DATE();                   

    DayNam = GetDayNam(DATE);   

    MonNam = GetMonNam(DATE);   

    DateNam = GetDatNam(DATE);  

    *INLR = *ON;                

 /END-FREE              

DayNam is loaded with "Thursday". MonNam is loaded with "January".  And DateNam is loaded with "Thursday, January 31 2008".

Even if you don't have a use for these specific functions, this technique illustrates how you can leverage SQL's wide range of capabilities and easily incorporate them into RPG programs.