Re: to_date()/to_timestamp() silently accept month=0 and day=0

On Wed, Apr 22, 2026 at 07:48:00PM +0530, Ayush Tiwari wrote:
> Inputs with month = 00 or day = 00 are accepted silently and normalized to
> January / day 1, instead of being rejected as out of range.
>
> Simple repro steps:
>
> SELECT to_date('2024-00-15', 'YYYY-MM-DD');
> SELECT to_date('2024-01-00', 'YYYY-MM-DD');
> SELECT to_timestamp('2024-00-15', 'YYYY-MM-DD');
> SELECT to_timestamp('2024-01-00', 'YYYY-MM-DD');
>
> Observed results here:
>
> to_date('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15
> to_date('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01
> to_timestamp('2024-00-15', 'YYYY-MM-DD') -> 2024-01-15 00:00:00+05:30
> to_timestamp('2024-01-00', 'YYYY-MM-DD') -> 2024-01-01 00:00:00+05:30
>
> I would expect all four calls to error, similar to how an invalid date
> literal is rejected.

While I agree with your feeling that it would be less confusing if
these patterns are rejected, throwing an error could also mean an
impact on existing applications that relied on the existing historical
behavior of replacing these zeroes defined in input, where they'd
expect a 01. So that would be a silent behavior change introduced in
a minor release.

Perhaps we could consider strengthening such inputs on HEAD once v20
opens for business? It would be really a scary thing to backpatch,
still a major release is a different thing.

Any thoughts or opinions from others?
--
Michael