public inbox for [email protected]
help / color / mirror / Atom feedFrom: Michael Paquier <[email protected]>
To: Ayush Tiwari <[email protected]>
Cc: [email protected]
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
Date: Thu, 23 Apr 2026 16:57:19 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
References: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
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
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox