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.