date function bug

ctrlmdb=> select to_date('2018100X','YYYYMMDD');
to_date

------------

2018-10-01
(1 row)

I am able to reproduce this in 11.5 It seems PG can take a single digit for Day too.
select to_date('2018109','YYYYMMDD') produces 2018-10-09.

On 10/23/19 9:22 AM, Abraham, Danny wrote:

Hi,

The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?

ctrlmdb=> select to_date('2018100X','YYYYMMDD');
to_date
------------
2018-10-01
(1 row)

psql (9.6.15)
Type "help" for help.

postgres=# select to_date('2018100X','YYYYMMDD');
  to_date
------------
 2018-10-01
(1 row)

postgres=#
postgres=# select to_date('2018150X','YYYYMMDD');
  to_date
------------
 2019-03-03
(1 row)

postgres=# select to_date('20181501','YYYYMMDD');
  to_date
------------
 2019-03-03
(1 row)

--
Angular momentum makes the world go 'round.

postgres=# select to_date('2018150X','YYYYMMDD');

  > to_date
------------

2019-03-03

postgres=# select to_date('20181501','YYYYMMDD');
  to_date
------------

  > 2019-03-03
is this a cut-n-paste mistake?

20181501 is illegal. Working OK.
ctrlmdb=> select to_date('20181501','YYYYMMDD')
ctrlmdb-> \g
ERROR: date/time field value out of range: "20181501"

From: Ravi Krishna <sr_krishna@aol.com>
Sent: Wednesday, October 23, 2019 5:28 PM
To: Abraham, Danny <danny_abraham@bmc.com>; pgsql-general@postgresql.org
Subject: [EXTERNAL] Re: date function bug

ctrlmdb=> select to_date('2018100X','YYYYMMDD');
to_date

------------

2018-10-01
(1 row)

I am able to reproduce this in 11.5 It seems PG can take a single digit for Day too.

select to_date('2018109','YYYYMMDD') produces 2018-10-09.

On 10/23/19 9:32 AM, Ravi Krishna wrote:

postgres=# select to_date('2018150X','YYYYMMDD');

  > to_date
------------

2019-03-03

postgres=# select to_date('20181501','YYYYMMDD');
  to_date
------------

  > 2019-03-03

is this a cut-n-paste mistake?

Nope.

Here's the screen print: http://i.imgur.com/f0UXfZh.png

--
Angular momentum makes the world go 'round.

From: Ravi Krishna <sr_krishna@aol.com>

postgres=# select to_date('2018150X','YYYYMMDD');
to_date

------------

2019-03-03

postgres=# select to_date('20181501','YYYYMMDD');
to_date
------------
2019-03-03

is this a cut-n-paste mistake?

====================
Surprisingly (to me), no….
db=# select to_date('20181501','YYYYMMDD');
to_date
------------
2019-03-03
(1 row)

Time: 0.497 ms
nms=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
(1 row)

Time: 0.247 ms
db=#

This e-mail transmission, and any documents, files or previous e-mail messages attached to it, may contain confidential information. If you are not the intended recipient, or a person responsible for delivering it to the intended recipient, you are hereby notified that any disclosure, distribution, review, copy or use of any of the information contained in or attached to this message is STRICTLY PROHIBITED. If you have received this transmission in error, please immediately notify us by reply e-mail, and destroy the original transmission and its attachments without reading them or saving them to disk. Thank you.

"Abraham, Danny" <danny_abraham@bmc.com> writes:

The function "to_date" does not fail illegal values.
Is this a known bug?

No, it's a feature, because the point of to_date() is to parse strings
that would be rejected or misinterpreted by the regular date input
function. If you want tighter error checking and your input is supposed
to follow a common format, just cast the string to date.

regression=# select '2018100X'::date;
ERROR: invalid input syntax for type date: "2018100X"
LINE 1: select '2018100X'::date;
^
regression=# select '20181501'::date;
ERROR: date/time field value out of range: "20181501"
LINE 1: select '20181501'::date;
^
HINT: Perhaps you need a different "datestyle" setting.
regression=# select '20181001'::date;
date
------------
2018-10-01
(1 row)

regards, tom lane

On 10/23/19 7:22 AM, Abraham, Danny wrote:

Hi,

The function "to_date" does not fail illegal values.
Is this a known bug?
What is the recommended type checking?

ctrlmdb=> select to_date('2018100X','YYYYMMDD');
to_date
------------
2018-10-01
(1 row)

At:

https://www.postgresql.org/docs/11/functions-formatting.html

I would read the section starting :

"Usage notes for date/time formatting: ..."

several times. There a lot of 'if and or buts' in there.

--
Adrian Klaver
adrian.klaver@aklaver.com

====================

Surprisingly (to me), no….

db=# select to_date('20181501','YYYYMMDD');
to_date
------------
2019-03-03

The above fails in 11.5 SQL Error [22008]: ERROR: date/time field value out of range: "20181501"

On 10/23/19 7:55 AM, Ravi Krishna wrote:

====================

Surprisingly (to me), no….

db=# select to_date('20181501','YYYYMMDD');
to_date
------------
2019-03-03

The above fails in 11.5 SQL Error [22008]: ERROR: date/time field value out of range: "20181501"

Behavior changed in v10:

https://www.postgresql.org/docs/10/release-10.html
"

Make to_timestamp() and to_date() reject out-of-range input fields
(Artur Zakirov)

For example, previously to_date('2009-06-40','YYYY-MM-DD') was accepted
and returned 2009-07-10. It will now generate an error.
"

--
Adrian Klaver
adrian.klaver@aklaver.com