RE: SQL date comparison - a better way?

[Kevin Mohondro <kevin.mohondro@xxxxxxxxxxxxxxx>]

Here you go. We place all of our UDFs in a service program called SQLUDF and
then register them through SQL using a CL program.

Here are the two we use to create dates out of numeric fields (depending on
whether you want an 8,0 value or an actual date value):

*******************************
     D CrtDat8         PR             8p 0                           
     D  PmCent                        1  0                           
     D  PmDate6                       6  0                           
                                                                     
     D CrtDat          PR              d                             
     D  PmCent                        1  0                           
     D  PmDate6                       6  0                           
                                                                     
     P CrtDat8         B                   Export                    
                                                                     
     D CrtDat8         PI             8p 0                           
     D  PmCent                        1  0                           
     D  PmDate6                       6  0                           
                                                                     
     D WkDat7          s              7p 0                           
     D WkISODate       s               d   datfmt(*ISO)              
     D RtnDat8         s              8p 0 Inz                       
                                                                     
     C                   Move      PmDate6       WkDat7              
     C                   MoveL     PmCent        WkDat7              
                                                                     
     C                   Monitor                                     
     C     *CYMD         Move      WkDat7        WkISODate           
     C                   Move      WkISODate     RtnDat8             
 

     C                   On-Error  00112
invalid date/timstmp        
     C                   EndMon

 

     C                   Return    RtnDat8

     P CrtDat8         E

 

      *====================================================================

     P CrtDat          B                   Export

 

     D CrtDat          PI              d

     D  PmCent                        1  0

     D  PmDate6                       6  0

 

     D WkDat7          s              7p 0

     D Today           s               d   inz(*job)

     D WkISODate       s               d   inz

 

     C                   Move      PmDate6       WkDat7

     C                   MoveL     PmCent        WkDat7

 

     C                   Monitor

     C     *CYMD         Test(d e)               WkDat7

     C     *CYMD         Move      WkDat7        WkISODate

 

     C                   On-Error  00112
invalid date/timstmp
     C                   EndMon

 

     C                   Return    WkISODate

     P CrtDat          E

*******************************
 SQLUDFCRT:  PGM                                           
             RUNSQL     REQUEST('DROP FUNCTION CRTDAT')    
             RUNSQL     REQUEST('DROP FUNCTION CRTDAT8')   

             RUNSQL     REQUEST('CREATE FUNCTION CRTDAT8 (DEC (1,0), +  
                          DEC (6,0)) RETURNS  DEC (8,0) LANGUAGE +      
                          RPGLE DETERMINISTIC NO SQL RETURNS  NULL  +   
                          ON NULL INPUT NO EXTERNAL ACTION ALLOW +      
                          PARALLEL  SIMPLE CALL EXTERNAL NAME +         
                          ''PRODLIB/SQLUDF(CRTDAT8)''')                  
                                                                        
             RUNSQL     REQUEST('CREATE FUNCTION CRTDAT  (DEC (1,0), +  
                          DEC (6,0) ) RETURNS  DATE +                   
                          LANGUAGE RPGLE +                              
                          NOT DETERMINISTIC +                           
                          NO SQL +                                      
                          RETURNS  NULL ON NULL INPUT +                 
                          NO EXTERNAL ACTION +                          
                          DISALLOW PARALLEL +                           
                          SIMPLE CALL EXTERNAL NAME +                   
                          ''PRODLIB/SQLUDF(CRTDAT)''')                   
             ENDPGM

Kevin R Mohondro
Programmer/Analyst
Ashworth, Inc.

#####################################################################################
Attention:
The information contained in this message and or attachments is intended
only for the person or entity to which it is addressed and may contain
confidential and/or privileged material.  Any review, retransmission,
dissemination or other use of, or taking of any action in reliance upon,
this information by persons or entities other than the intended recipient
is prohibited. If you received this in error, please contact the sender and
delete the material from any system and destroy any copies.

Thank You.
#####################################################################################