Re: Bug report function to_date should reject HH24:MI:SS formatting options

It makes sense.

Thank you Tom for your answer and your work on open source projects.

regards,

Sébastien Caunes

Le jeu. 30 sept. 2021 à 15:35, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> a écrit :

> =?UTF-8?Q?S=C3=A9bastien_Caunes?= <sebastien(at)pixseed(dot)fr> writes:
> > TO_DATE function should reject the time part (HH24:MI:SS) in the template
> > string.
>
> We're not going to do that, because it would break interesting use-cases.
> It's true that you could just omit the time part of the format in the
> example you gave. But if you want to parse data with the time part
> appearing first, that doesn't work:
>
> regression=# select TO_DATE('08:30:37 2021-09-30', 'HH24:MI:SS
> YYYY-MM-DD');
> to_date
> ------------
> 2021-09-30
> (1 row)
>
> regression=# select TO_DATE('08:30:37 2021-09-30', 'YYYY-MM-DD');
> ERROR: date/time field value out of range: "08:30:37 2021-09-30"
>
> > I just saw developers coming from Oracle spending a lot of time on this.
>
> A rule of thumb for people coming from Oracle is that uses of to_date
> and related functions should be nuked from orbit. For just about any
> common datetime layout, simply casting to the desired type is safer
> and easier:
>
> regression=# select '2021-09-30 08:30:37'::timestamp;
> timestamp
> ---------------------
> 2021-09-30 08:30:37
> (1 row)
>
> You only need to_date and friends to parse weird formats (like
> time-of-day first). Our docs do make this point, but perhaps
> not forcefully enough.
>
> regards, tom lane
>