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
midrange.com
