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