Thread: date function bug

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)

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.

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

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