RE: Using SQL to convert date

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Very nice Birgitta.  Gotta love those CEE apis.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: AW: Using SQL to convert date

Instead of writing a C-function, you may also Create an RPG (or Cobol)
function and use the CEE-APIs.
After register this function as SQLUDF

1. Prototype for CEEDAYS: Convert Date String into lilian days
D CEEDAYS         PR                  OpDesc                              
D  PInDateString               256A   varying Const                       
D  PInPictString               256A   varying Const                       
D  POutLilSecs                  10I 0                                       
D  POutFeedBack                 12A   Options(*Omit)  

2. Prototype for CEEDATE: Convert Lilian Date into String
D CEEDATE         PR                                   OpDesc              
D  ParLilDate                   10I 0           Const                     
D  ParString                   256A   Varying   Const  Options(*VarSize)  
D  POutCharDate                256A   Varying          Options(*VarSize)  
D  ParFeedBack                  12A                    Options(*Omit)     

----------------------------------------------------------------------------
H NoMain
 ***************************************************************************
P CvtCharDateToNum...

P                 B                   Export

D CvtCharDateToNum...

D                 PI             8P 0 Varying

D   ParDate                     15A   Varying Const

 

D LocLilDate      S             10I 0

D LocCharDate     S            256A   Varying

 *-------------------------------------------------------------------------
 /Free

    CEEDAYS(%Trim(ParDate): 'ZD/Mmm/YYYY': LocLilDate: *Omit);            
    CEEDATE(LocLilDate: 'YYYYMMDD': LocCharDate: *Omit);

    Return %Dec(LocCharDate: 8: 0);

 /End-Free                                                                
P CvtCharDateToNum...                                                    
P                 E


Compilation and registration of this function is identical to Elvis'
compilation, except CRTRPGMOD is used and the language in the SQL command
Create functions is RPGLE and use parameter style General.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser