Just as an aside: SQL can convert any date with a 4 digit year in one of the
following formats 'YYYY-MM-DD', 'DD.MM.YYYY', 'MM/DD/YYYY' independent
whether day and/or month name are specified with a single digit or 2 digits.
It does not work for any date with a 2 digit year.
AFAIK in RPG (without) SQL, Dates are handled in the same way.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> On Behalf Of John Yeung
Sent: Samstag, 5. Mai 2018 01:03
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: m/d/yyyy string to yyyymmdd decimal in SQL
On Fri, May 4, 2018 at 6:05 PM, Dan <dan27649@xxxxxxxxx> wrote:
[string M/D/YYYY -> decimal YYYYMMDD]
The following does it, but it seems to me that there should be an
easier way, utilizing less than four functions to do the conversion. Is
there?
select BI_DOSA,
Dec( Replace(
Cast( Date(TIMESTAMP_FORMAT( BI_DOSA, 'MM/DD/YYYY'))
as Char(10)),
'-', ''),8)
from CA8560BIP
Well, maybe? On our 7.1 machine, you can dispense with the TIMESTAMP_FORMAT.
The DATE function converts the string directly. Big
caveat: I can't remember if Barbara said that this only incidentally works
on single-digit months and days. In some context or another (so I can't be
sure it applies here), the intended behavior was that two-digit months and
days were required. And it's only an accident and not guaranteed that
single-digit inputs will be accepted.
For that matter, is TIMESTAMP_FORMAT guaranteed to accept single-digit
months and days? If neither DATE nor TIMESTAMP_FORMAT do, then it gets even
hairier.
Additionally, job or system settings come into play. Clearly your dates are
coming out in ISO format when cast as CHAR(10). But on our system, we get
MM/DD/YY. So, to do the equivalent of what you're trying to do, on our
system, it's more like
select BI_DOSA,
dec(replace(char(date(BI_DOSA), iso), '-', ''), 8)
from CA8560BIP
where I use the CHAR function instead of your cast, and skip
TIMESTAMP_FORMAT.
John Y.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
