RE: Using SQL to convert date

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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.