Re: m/d/yyyy string to yyyymmdd decimal in SQL

On Fri, May 4, 2018 at 7:02 PM, John Yeung <gallium.arsenide@xxxxxxxxx>
wrote:

On Fri, May 4, 2018 at 6:05 PM, Dan <dan27649@xxxxxxxxx> wrote:

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.

Interesting. I tried that as well on our 7.1 box, and you're right, it
works. In the documentation for the DATE function, if a string is used, it
must be "A valid string representation of a date or timestamp." It's
followed by a link to "String representations of datetime values" which
includes the following: "Leading zeros can be omitted from the month and
day portions when using the IBM SQL standard formats." So, it would appear
that this is supported by the documentation, although the examples given
for the DATE function don't demonstrate this.

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.

The documentation for TIMESTAMP_FORMAT does, in fact, explicitly handle the
1- and 2-digit months and days, and demonstrates this with examples.

- Dan