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
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Elvis Budimlic
Gesendet: Thursday, 03. September 2009 23:29
An: 'Midrange Systems Technical Discussion'
Betreff: RE: Using SQL to convert date
Staying with SQL, even with SUBSTRINGs and CASE statements will probably
perform better.
However, external UDFs are just cooler :)
Per Charles Wilt's inspiration, try this:
/* START C SOURCE */
#include <time.h>
#include <decimal.h>
void getNumericDate(
char * inDate,
decimal(8,0) * out,
short * ind1,
short * outind,
char * sqlstate,
char * funcname,
char * specname,
char * msgtext)
{
struct tm tm;
char buffer[9] = {0};
strptime(inDate,"%d/%b/%Y",&tm);
strftime(buffer,sizeof(buffer)-1,"%Y%m%d",&tm);
*out = atoi(buffer);
outind = 0;
}
/* END C SOURCE */
/* compile C source into a MODULE object */
CRTCMOD MODULE(MYLIB/MYSRVPGM) SRCFILE(MYLIB/QCSRC)
/* create a *SRVPGM to be used as an external UDF target */
CRTSRVPGM SRVPGM(MYLIB/MYSRVPGM) EXPORT(*ALL)
STRSQL
/* create an external UDF to leverage in your SQL code */
CREATE FUNCTION QGPL/getNumericDate(inDate varchar(11))
RETURNS DECIMAL(8,0)
RETURNS NULL ON NULL INPUT
NOT FENCED
NO SQL
NO EXTERNAL ACTION
DETERMINISTIC
LANGUAGE C
PARAMETER STYLE SQL
EXTERNAL NAME 'MYLIB/MYSRVPGM(getNumericDate)'
/* test UDF */
SELECT getNumericDate(myMessyDateField) FROM myLib/myFile
Or do the same in RPG if you prefer.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: Re: Using SQL to convert date
Dean,
Consider building a UDF around the strptime()? Convert String to Date/Time
API.
HTH,
Charles
On Thu, Sep 3, 2009 at 3:09 PM, <Dean.Eshleman@xxxxxxxxxxxxxx> wrote:
Hi,
Using SQL, I'm trying to figure out how to convert a character field that
contains '27/Aug/2009' into an 8 digit numeric date in the format
20090827. After the conversion, I want to load it into a field in the
same record. I know I can substring out the year and the day. The tough
part is converting the month. Is a CASE statement my only option? TIA
Dean Eshleman,
MMA, Inc.
midrange.com
