Feedback-ID: i0fe9450f:Fastmail
Date: Thu, 23 Apr 2026 16:57:19 +0900
From: Michael Paquier <michael@paquier.xyz>
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: to_date()/to_timestamp() silently accept month=0 and day=0
Message-ID: <aenQ37wBgKqZb7a8@paquier.xyz>
References: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="HlIKK0q25Kt6Z2yN"
Content-Disposition: inline
In-Reply-To: <CAJTYsWWNfF+hMpbsTDF8NMr+AuqoDfNxR=oHUZm7xqGP+dJ9rA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/aenQ37wBgKqZb7a8%40paquier.xyz>
Precedence: bulk


--HlIKK0q25Kt6Z2yN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Wed, Apr 22, 2026 at 07:48:00PM +0530, Ayush Tiwari wrote:
> Inputs with month =3D 00 or day =3D 00 are accepted silently and normaliz=
ed to
> January / day 1, instead of being rejected as out of range.
>=20
> Simple repro steps:
>=20
> 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');
>=20
> Observed results here:
>=20
>   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
>=20
> 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

--HlIKK0q25Kt6Z2yN
Content-Type: application/pgp-signature; name=signature.asc

-----BEGIN PGP SIGNATURE-----

iQIzBAEBCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmnp0N4ACgkQnvQgOdby
QH0EPQ/9FzSqn6b9kwn/acTjK0oLB7sapxvc0gozZl6CMaNJN8of/KSg55StJ39R
c0wr3VxznP5f+ejAKSPRdlH992LEjUXWSqNDQ88drkNxHIz0+Cfw96liOWUeLlZR
zFDAGOub/403ecFs/w0theD8qVKblhm4Ml2LYdYka2vcUsDSBqLQrlhuBPIqx5i9
gL4juB11PjaWYC0UAbHQ+JaLcHCkO55xFw04SyYBtj+zPjqtgIXDScmONs0Vp0lA
fmWxMqtlvxAMx1TpzjAWGUX6CTsyGgzE7J8Jro3JVUdHAvoa//MWyOgKfNCuObLI
PhmyOYRCiSvsKO8Dr0buQpBIVOycpcRPKJmhOshCJhQ85Lt9TvUt4RTL/hNWsPhq
ZKjnZQIXOkcfmBsgdyx1gDhdNufJMRrZGgkyPet62GoZ8kvJ19RaIeI9s3OS6zT0
hhK2BBzKFIW2v6drTp9dCFFZUjq0Ybd8luHuB5OwHV3949Pa3dDwaKQPgp7Anyu+
AkMXvj/FybRCfvm5CMZpWI7R0dfYpkhZXI2pFJ4HszuayA7PGvUxy4cTA9z7U0Ak
kwjqxJIxB9uoyTOiT2fUnuDHgNOlchT9MowNBKuvIPdWSNnsXvCdTkdpWQ3tsPmu
E5qB113DPIfOVCT0nmja4SEXuWW74TFTNJ3ayJJEDkch8DhlspA=
=KUHg
-----END PGP SIGNATURE-----

--HlIKK0q25Kt6Z2yN--