Re: to_date_valid()

On 08.09.2016 17:31, Peter Eisentraut wrote:
> On 8/15/16 7:33 AM, Andreas 'ads' Scherbaum wrote:
>> postgres=# SELECT to_date('2011 12 18', 'YYYY MM DD');
>> to_date
>> ------------
>> 2011-12-08
>> (1 row)
>>
>>
>> That is from the regression tests, and obviously handles the date
>> transformation wrong. My attempt catches this, because I compare the
>> date with the input date, and do not rely on a valid date only.
>
> It's debatable what is correct here.
>
> Using to_number, the behavior appears to be that a space in the pattern
> ignores one character. For example:
>
> test=# select to_number('123 456', '999 999');
> to_number
> -----------
> 123456
>
> test=# select to_number('123 456', '999 999');
> to_number
> -----------
> 12356
>
> Considering that, the above to_date result is not incorrect.
>
> So just squashing the spaces and converting the value back is not a
> correct approach to detecting overflow.
>
> I think using ValidateDate() was the right idea. That is what we use
> for checking date validity everywhere else.

ValidateDate() will tell you if it's a valid date. But not if the
transformation was correct:

postgres=# SELECT to_date('2011 12 18', 'YYYY MM DD');
to_date
------------
2011-12-08
(1 row)

(with the patch from Artur)

Any idea how to solve this problem?

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project