Обсуждение: TO_DATE Function unintended behavior when month value is greater than 12
select to_date('20171231','YYYYDDMM')
result:
2019-07-17
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
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: 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
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)
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
Thank you all for the update. I checked the version details. it is "PostgreSQL 8.0.2".
"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
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
>>>>> "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)