On 29-Oct-2014 16:34 -0500, Stone, Joel wrote:
<<SNIP>>
I should have mentioned this particular iseries in on v5r4, which
doesn't support the Timestamp_Format <<SNIP>>
Any ideas what to do on v5r4?
Is the simplest method to use the CASE statement to build two
conditions? (One for single digit month and another for 2 digit
month)?
The simplest is probably a[n existing] UDF; e.g. the iDate utility
that has already been mentioned. As for a non-UDF solution...
The _CASE expression_ [not the CASE statement] could be used to
handle the scenario. However there are three variants of single-digit
that need to be handled; a single-digit either occurs once [but for two
distinct variations of the format], or occurs twice [for an additional
distinct variation]. The forth variation is where all values have both
digits.
IMO modifying the value of the field to a *USA format using the CASE
expression should be much simpler than trying to condition the three
potential variants for no leading digit(s). That is because, as
Birgitta noted [and I presumed so, but saw no supporting doc; though
verified with anecdotal testing] that all of the 4-digit [standards]
formats support the lack of leading zeroes just as do the Timestamp Strings.
The scenario is effectively: convert the character-string Date data
into a TimeStamp value when the given character string data has been
formatted per DATFMT(*MDY) DATSEP('/') but wherein the values might be
[ill-]formed like any of 'M/D/YY', 'MM/D/YY', or 'M/DD/YY' rather than
like 'MM/DD/YY'; some specific examples are [paraphrased] that each of
'01/02/03', '1/02/03', '01/2/03', and '1/2/03' should convert to the
internal\integer date data type value represented by a timestamp for
02-Jan-2003 at the zero hour.
Note: the format implies 8-bytes, and the assumptions made are that
the character data values are left-justified.
For the DATE values:
date( insert( d8
, locate('/', d8, 4) + 4
, 0
, case when right( rtrim(d8), 2 ) between '40' and '99'
then '19'
else '20'
end
)
)
/* rtrim() was added, because the OP implied 10 vs 8 bytes */
/* according to assumptions, last 2-bytes are always blank */
For the TIMESTAMP values:
timestamp( the_above_expression_for_DATE
, '00.00.00')
If the SQL environment is going to be DATFMT(*MDY) DATSEP('/'), then
either of the following CASE expressions would suffice to generate the
date string with the leading zeroes in the aforementioned /ill-formed/
2-digit-year MDY:
case translate(d8, ' ', '0123456789')
when ' / / ' then '0' concat insert( d8, 3, 0, '0' )
when ' / / ' then insert( d8, 4, 0, '0' )
when ' / / ' then '0' concat d8
else d8 /* assumed: when ' / / ' */
end as mmddyy
case when substr(d8, 2, 1) = '/' and substr(d8, 4, 1) = '/'
then '0' concat insert( d8, 3, 0, '0' )
when substr(d8, 3, 1) = '/' and substr(d8, 5, 1) = '/'
then insert( d8, 4, 0, '0' )
when substr(d8, 2, 1) = '/' and substr(d8, 5, 1) = '/'
then '0' concat d8
else d8 /* assumed: when %sst pos 3 and 6 are = '/' */
end as mmddyy
If the data were mine, I would aim to /correct/ the programs to
insert and update with data that includes the leading zero in every part
of the *MDY date string values, not just for the year component;
consistent\fixed positions allows for overlaying the character digits as
numeric or simply as the distinct components of the data within the
character string. And given the values are already apparently being
stored in 10-bytes, I would see about consistently filling that field
with a full four-digit year date string value instead of using the
deprecated [minimally since last century] two-digit year format. And I
would choose ISO formatting instead of *USA, if keeping with a character
data type, because that allows for /natural/ collation. Regardless, I
would add a CHECK CONSTRAINT to a column that would ensure the character
string is a valid date.
midrange.com
