On 28-Jun-2016 16:17 -0500, Buck Calabro wrote:
<<SNIP>>
But if you want to roll your own, you need to convert the pack to
char, then substring out all the pieces, assemble them with ISO
editing and perform a DATE() over that. I have an RPG sub-procedure
that does all of that, similar to IDATE.
While that is _a_ way to accomplish the task, that is also probably
one of the more complicated ways; using a string representation of the
/date/ without separators is less work. And starting with a simple
arithmetic expression can be /better/, even if just for enabling a value
for C [of CYYMMDD] BETWEEN 0 AND 9 to enable conversion for, extending,
the domain of dates spanning 1900-01-01 to 2899-12-31
I have a tiny test file I use for testing this stuff; it has a field
called BINARY which holds dates in CYYMMDD form.
Knowing the precision and scale is often imperative, for ensuring a
proper expression to convert from a numeric value to a DATE value.
Note: BINARY is a reserved word, so the references to the field name
are best made as "BINARY" to avoid errors in composing any SQL requests
that might refer to that identifier. Better yet, choose a field name
that is not a reserved word :-)
Here are a couple variations of such a table:
create table datesampl7
( "BINARY" dec(7) )
;
create table datesamplA
( "BINARY" int )
;
insert into datesampl7 values
( 0180401 ), ( 0960301 ), ( 1000229 ), ( 1160628 )
, ( 1991231 ), ( 2990101 ), ( 9990101 ), ( 9991231 )
;
insert into datesamplA select * from datesampl7
;
Here is an SQL statement that looks like it'll do the conversion:
select binary,
date(
case
when substr(char(binary), 1, 1) = '1' then '20'
else '19'
end concat
substr(char(binary), 2, 2) concat '-' concat
substr(char(binary), 4, 2) concat '-' concat
substr(char(binary), 6, 2)
) as truedate
from buck.datesample
where binary <> 0;
If the input is 1160628, the output is 2016-06-28
<<SNIP>>
The above expression against the data in my sample variants [i.e. the
above query with the table-reference modified in the FROM clause to be
either table created\populated in my earlier script] gives mapping
errors for the first two values, and assigns the '19' prefix [as
incorrect output] for the last three values for which the value of C [of
CYYMMDD] is greater than one.
Darryl already alluded to that flaw diagnosed as a mapping error,
arising per use of the CHAR vs DIGITS scalar; using CHAR makes the above
expression fail for values outside of the domain of the numeric data
with date representations 2000-01-01 to 2099-12-31, due to there being
no leading zero for those values, thus the substring start-positions are
invalid. That effect conspicuously omits support for all of the dates
in the 1900s, invalidating the purpose of the C=0 of the CYYMMDD :-( To
/fix/ that in the above expression requires that DIGITS be used in the
first argument of the SUBSTR scalars with constant start-positions; at
least to avoid more CASE logic. I figure no point in offering the
revised, and instead offer:
A /better/ solution [albeit not nearly as conspicuous for the effect,
as with the formatting of an *ISO date-string], is to take advantage of
the 14-digit string format [i.e. no separators] for representation of a
TIMESTAMP:
date(digits(dec("BINARY"+19000000 , 8)) concat '000000' )
And while Mike referred to the TIMESTAMP_FORMAT [aka TO_DATE and
TO_TIMESTAMP] in
[
http://archive.midrange.com/midrange-l/201606/msg00544.html], just like
the arithmetic expression can be easier than a string expression coming
from numeric, the numeric value is easier tested in the CASE expression
as a numeric than as an expression for a string result; again [and most
likely not an issue], this CASE reduces the C of CYYMMDD to support for
just values of '0' and '1', and would produce incorrect output for any
larger digit:
date( to_date( case when "BINARY" > 1000000 then '20'
else '19'
end concat substr( digits( "BINARY" ), 2 )
, 'YYYYMMDD' ) )
midrange.com
