RE: Using SQL to convert date

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