Postgres Pro
Downloads
Home   >   mailing lists

Thread: TO_DATE Function unintended behavior when month value is greater than 12

TO_DATE Function unintended behavior when month value is greater than 12

From
naidu rongali
Date:
Hi,
I noticed this behavior on recent release of PG database. It will be helpful to users, if this behavior is looked into. 

When executed below SQL, got unintended output when i expected it to throw error " not a valid month "

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17


Thank you, Naidu

Re: TO_DATE Function unintended behavior when month value is greaterthan 12

From
Pavel Stehule
Date:


2018-03-01 19:34 GMT+01:00 naidu rongali <rongalinaidu@gmail.com>:
Hi,
I noticed this behavior on recent release of PG database. It will be helpful to users, if this behavior is looked into. 

When executed below SQL, got unintended output when i expected it to throw error " not a valid month "

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

What is your version?

It should be fixed on PostgreSQL 10 and higher (there was not backport because some applications used this feature)

Regards

Pavel



 


Thank you, Naidu

RE: TO_DATE Function unintended behavior when month value is greaterthan 12

From
Igor Neyman
Date:

 

From: naidu rongali [mailto:rongalinaidu@gmail.com]
Sent: Thursday, March 01, 2018 1:34 PM
To: pgsql-bugs@postgresql.org
Subject: TO_DATE Function unintended behavior when month value is greater than 12

 

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

 

Hi,

I noticed this behavior on recent release of PG database. It will be helpful to users, if this behavior is looked into. 

 

When executed below SQL, got unintended output when i expected it to throw error " not a valid month "

 

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

 

 

Thank you, Naidu

 

I get this:

ERROR: date/time field value out of range: "20171231" SQL state: 22008

 

Regards,

Igor Neyman

 

Re: TO_DATE Function unintended behavior when month value is greaterthan 12

From
Pantelis Theodosiou
Date:


On Thu, Mar 1, 2018 at 7:28 PM, Igor Neyman <ineyman@perceptron.com> wrote:

 

From: naidu rongali [mailto:rongalinaidu@gmail.com]
Sent: Thursday, March 01, 2018 1:34 PM
To: pgsql-bugs@postgresql.org
Subject: TO_DATE Function unintended behavior when month value is greater than 12

 

Attention: This email was sent from someone outside of Perceptron. Always exercise caution when opening attachments or clicking links from unknown senders or when receiving unexpected emails.

 

Hi,

I noticed this behavior on recent release of PG database. It will be helpful to users, if this behavior is looked into. 

 

When executed below SQL, got unintended output when i expected it to throw error " not a valid month "

 

SQL executed:
select to_date('20171231','YYYYDDMM')

result:
2019-07-17

 

 

Thank you, Naidu

 

I get this:

ERROR: date/time field value out of range: "20171231" SQL state: 22008

 

Regards,

Igor Neyman

 


The error is thrown in version 10 but I get the wrong results in versions 9.3 to 9.6, both in the almost latest (9.3.21, 9.4.16, 9.5.11 and 9.6.7) and today's releases (9.3.22, ..., 9.6.8)

Pantelis Theodosiou

Re: TO_DATE Function unintended behavior when month value is greaterthan 12

From
"David G. Johnston"
Date:

The error is thrown in version 10 but I get the wrong results in versions 9.3 to 9.6, both in the almost latest (9.3.21, 9.4.16, 9.5.11 and 9.6.7) and today's releases (9.3.22, ..., 9.6.8)


​As Pavel said we fixed this in 10 but did not back-patch the behavior change.

David J.
​

Re: TO_DATE Function unintended behavior when month value is greaterthan 12

From
naidu rongali
Date:
Thank you all for the update. I checked the version details.  it is "PostgreSQL 8.0.2". 

On Fri, Mar 2, 2018 at 1:13 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

The error is thrown in version 10 but I get the wrong results in versions 9.3 to 9.6, both in the almost latest (9.3.21, 9.4.16, 9.5.11 and 9.6.7) and today's releases (9.3.22, ..., 9.6.8)


​As Pavel said we fixed this in 10 but did not back-patch the behavior change.

David J.
​

TO_DATE Function unintended behavior when month value is greater than 12

From
"David G. Johnston"
Date:
On Thursday, March 1, 2018, naidu rongali <rongalinaidu@gmail.com> wrote:
Thank you all for the update. I checked the version details.  it is "PostgreSQL 8.0.2". 

If that database is at all important to anyone it needs serious professional attention.  It doesn't qualify as "recent" no matter how liberal you wish to make the definition.

David J.

Re: TO_DATE Function unintended behavior when month value is greater than 12

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, March 1, 2018, naidu rongali <rongalinaidu@gmail.com> wrote:
>> Thank you all for the update. I checked the version details.  it is
>> "PostgreSQL 8.0.2".

> If that database is at all important to anyone it needs serious
> professional attention.  It doesn't qualify as "recent" no matter how
> liberal you wish to make the definition.

To quantify that: 8.0.2 was released on 2005-04-07, and was obsoleted
by 8.0.3 on 2005-05-09.  See release notes at
https://www.postgresql.org/docs/8.0/static/release.html

            regards, tom lane

Re: TO_DATE Function unintended behavior when month value is greaterthan 12

From
"David G. Johnston"
Date:
On Fri, Mar 2, 2018 at 6:52 AM, naidu rongali <rongalinaidu@gmail.com> wrote:
though 8.2 is in deprecated versions, looks like 8.0.2 is released on 2009-02-02  as per https://www.postgresql.org/docs/8.0/static/release-8-0-20.html 


​That link points to 8.0.20 not 8.0.2 ... it would less egregious if running 8.0.26 since at least you would have been running the most up-to-date version of the 8.0 major release series but even that is over 7 years old at this point.

8.2[.x] is a completely different major version.

Upgrading 8.0 to a late 9 series or current 10 release will be non-trivial but, IMO, the decision not to should be a done with the consent and understanding of whomever ultimately owns the database and not through inattention.

David J.

Re: TO_DATE Function unintended behavior when month value is greater than 12

From
Andrew Gierth
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >>> Thank you all for the update. I checked the version details.  it is
 >>> "PostgreSQL 8.0.2".

 >> If that database is at all important to anyone it needs serious
 >> professional attention.  It doesn't qualify as "recent" no matter how
 >> liberal you wish to make the definition.

 Tom> To quantify that: 8.0.2 was released on 2005-04-07, and was obsoleted
 Tom> by 8.0.3 on 2005-05-09.  See release notes at
 Tom> https://www.postgresql.org/docs/8.0/static/release.html

It's probably not postgresql at all; Amazon Redshift identifies itself
as pg 8.0.2, even though it supports a lot of more recent stuff as well.

-- 
Andrew (irc:RhodiumToad)