Re: to_date() and to_timestamp() with negative years

On 11/3/21 19:12, Bryn Llewellyn wrote:
>> /adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> 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@aklaver.com