On 11/3/21 19:12, Bryn Llewellyn wrote:
/adrian.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx> wrote:/
Back to the point about separators, the "Current" doc has this bullet:
«
A separator (a space or non-letter/non-digit character) in the template
string of to_timestamp and to_date matches any single separator in
the input string or is skipped, unless the FX option is used...
»
(There's no such explanation in the Version 11 doc—but never mind that.)
I read this to mean that a space IS a viable separator. And yet Tom's
"nope, the space doesn't count [as a separator]" claims the opposite.
The bullet's wording, by the way, says that the minus sign is a
separator. But yet it can also be taken to confer the meaning "BC" to a
year. No wonder I'm confused.
Elsewhere the "Current" doc says that runs of two or more spaces have
the same effect as a single space (in the absence of FX or FM complexity).
No wonder that examples like I showed sometimes produce the wrong
results, even after more than one round of tinkering with the C
implementation. Try this test (the to-be-converted text has runs of five
spaces, and the template has runs of ten spaces):
It comes down to determining what is a separator and what is the
negative sign.
select to_date(' 1950 02 14', 'YYYY MM DD');
select to_date(' -1950 02 14', 'YYYY MM DD');
The row above returns:
select to_date(' -1950 02 14', 'YYYY MM DD');
to_date
---------------
1950-02-14 BC
As you state below.
Change it to:
select to_date(' -1950 02 14', ' YYYY MM DD');
to_date
------------
1950-02-14
and you don't get the BC as -1950 is taken as separator(-)1950 not
negative(-)1950.
select to_date(' 14 02 1950', 'DD MM YYYY');
select to_date(' 14 02 -1950', 'DD MM YYYY');
The above returns:
select to_date(' 14 02 -1950', 'DD MM YYYY');
to_date
------------
1950-02-14
Change it to:
select to_date(' 14 02 -1950', 'DD MMYYYY');
to_date
---------------
1950-02-14 BC
and you get BC as -1950 is taken as negative(-)1950 not separator(-)1950.
Not sure how this can be handled in a totally predictable way given the
unpredictable ways in which datetime strings are formatted?
The only thing I can say it is it points out that when working with
datetimes settling on a standard format is your best defense against
unpredictable results.
"-1950" is taken as "BC" in the second to_date() but it is not so taken
in the fourth to_date().
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
