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
