Re: Using SQL to convert date

[CRPence <CRPbottle@xxxxxxxxx>]

Dean.Eshleman wrote:
> 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?

  Since the list of values for month is small [and static], a CASE 
seems to be a reasonable option; except to avoid coding in multiple 
places.  However, retrieving matching month data from a [temporary] 
TABLE is an option as well.  In either case, the character /month/ 
value can be converted into a numeric month value [or a character 
string that represents the number of the month].  The only 
difficulty should be for any consistency or validity issues with the 
character date value; a data problem really, rather than a 
difficulty.  Any character folding\casing is easily resolved using 
the scalar UPPER() [e.g. 'AUg' vs 'Aug'] and the ELSE for a CASE 
enables giving the NULL [or an alternate non-NULL] value for the 
numeric representation of the date value [e.g. a special number that 
represents /not a valid date/ error condition].

<code>

  create table DateMods
   ( cDate char(11)
 /* -- should have constraints to prevent bad data; e.g. */
 /*, check substr(cDate, 4, 3) in ('Jan','Feb',...'Dec') */
   , nDate dec (8, 0)  )
  ;
  insert into  DateMods (cDate)
    values('27/Aug/2009')
         ,('01/Dec/1999')
  ;
  /* Try embed static table in update [like CASE], but
     likely WITH [& VALUES til 6.1] is not valid, so
     use a previously created months table; see next    */
  update DateMods
    set nDate = /* optionally IFNULL to a special value */
           substr(cDate, 8, 4) * 10000
         + (with CharMonths (M, Mmm) as
              ( values(0100, 'Jan')
                    , (0200, 'Feb')
                         ...
                    , (1200, 'Dec') )
            select M from CharMonths
            where Mmm = substr(cDate, 4, 3)
           )
         + substr(cDate, 1, 2)
   ;
   /* create the _months_ table to correlate name to number */
   create table CharMonths
   ( M decimal(4, 0)
   , Mmm char(3)    )
   ;
   insert into CharMonths (M, Mmm)
   values(0100, 'Jan')
       , (0200, 'Feb')
              ...
       , (1200, 'Dec')
   ;
   /* using subselect to access existing months table   */
   update DateMods
    set nDate = /* optionally IFNULL to a special value */
           substr(cDate, 8, 4) * 10000
         + (select M from CharMonths
            where Mmm = substr(cDate, 4, 3) )
         + substr(cDate, 1, 2)
   ;
   call qsys.qcmdexc('runqry *none DateMods', 0000000000.00000)
   /* produces a report like:
     cDate        nDate
     27/Aug/2009  20090827
     01/Dec/1999  19991201    */
   ;
   /* And of course, using the CASE */
   update DateMods
    set yyyymmdd = /* optionally IFNULL to a special value */
           substr(cDate, 8, 4) * 10000
         + case substr(cDate, 4, 3)
            when 'Jan' then 0100
            when 'Feb' then 0200
                         ...
            when 'Dec' then 1200
            /* else is the NULL value */
           end
         + substr(cDate, 1, 2)
   ;

</code>

Regards, Chuck