BUG #1630: Wrong conversion in to_date() function. See example.

On Tue, Apr 26, 2005 at 11:48:12PM +0100, Ariel E. Carná/Elizabeth Sosa wrote:

Case PgSQL 7.3.8/SuSE Linux 8.2 (i586)
======================================
PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
to_date
------------
2005-03-04
(1 row)

Case PgSQL 7.4.6/SuSE Linux 9.2
(i586)======================================
PROD=> select to_date('2005-02-32', 'YYYY-MM-DD');
to_date
------------
04-03-2005
(1 row)

What bug are you reporting? These are the same date displayed in
different formats -- are you familiar with the DateStyle setting?

http://www.postgresql.org/docs/7.3/interactive/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT
http://www.postgresql.org/docs/7.4/interactive/datatype-datetime.html#DATATYPE-DATETIME-OUTPUT

test=> SELECT version();
version
---------------------------------------------------------------------------
PostgreSQL 7.4.7 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)

test=> SET DateStyle TO 'ISO,MDY';
SET
test=> SELECT to_date('2005-02-32', 'YYYY-MM-DD');
to_date
------------
2005-03-04
(1 row)

test=> SET DateStyle TO 'Postgres,DMY';
SET
test=> SELECT to_date('2005-02-32', 'YYYY-MM-DD');
to_date
------------
04-03-2005
(1 row)

If anything I'd expect 2005-02-32 to be rejected as invalid, but I
don't know the history or rationale behind to_date's behavior.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

"Ariel E. Carná/Elizabeth Sosa" <acarna@tarifar.com> writes:

PROD=# select to_date('2005-02-32', 'YYYY-MM-DD');
to_date
------------
2005-03-04
(1 row)

I'm not convinced that's a bug --- most implementations of the Unix
mktime function will handle out-of-range day numbers like that.

regards, tom lane

Even javascript handles those dates the same way...

Michael Fuhr <mike@fuhr.org> writes:

If anything I'd expect 2005-02-32 to be rejected as invalid, but I
don't know the history or rationale behind to_date's behavior.

It is rejected by the standard date input converter:

regression=# select '2005-02-32'::date;
ERROR: date/time field value out of range: "2005-02-32"
HINT: Perhaps you need a different "datestyle" setting.

However we consider that to_date() exists to be Oracle compatible,
and so I would regard this as a bug if and only if Oracle does
something different with the same input. Anyone know?

regards, tom lane

With Oracle (at least since 6.x version) this conversion is wrong.

Atentamente ,

Ariel Carná

-----Mensaje original-----
De: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Enviado el: Miércoles, 27 de Abril de 2005 11:54 a.m.
Para: Michael Fuhr
CC: Ariel E. Carná/Elizabeth Sosa; pgsql-bugs@postgresql.org
Asunto: Re: [BUGS] BUG #1630: Wrong conversion in to_date() function. See
example.

Michael Fuhr <mike@fuhr.org> writes:

If anything I'd expect 2005-02-32 to be rejected as invalid, but I
don't know the history or rationale behind to_date's behavior.

It is rejected by the standard date input converter:

regression=# select '2005-02-32'::date;
ERROR: date/time field value out of range: "2005-02-32"
HINT: Perhaps you need a different "datestyle" setting.

However we consider that to_date() exists to be Oracle compatible,
and so I would regard this as a bug if and only if Oracle does
something different with the same input. Anyone know?

regards, tom lane

However we consider that to_date() exists to be
Oracle compatible,
and so I would regard this as a bug if and only if
Oracle does
something different with the same input. Anyone
know?

Here is the output I get from Oracle:
=====================================================

Connected to:
Oracle8 Enterprise Edition Release 8.0.6.3.0 -
Production

SELECT to_date('2005-02-27', 'YYYY-MM-DD') from dual
TO_DATE('2005-02-27','YYYY-MM-
------------------------------
2/27/2005
1 row selected

SELECT to_date('2005-02-29', 'YYYY-MM-DD') from dual
ORA-01839: date not valid for month specified

SELECT to_date('2005-02-32', 'YYYY-MM-DD') from dual;
ORA-01847: day of month must be between 1 and last day
of month

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

Regards,

Shelby Cain

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

On Wed, 2005-04-27 at 10:53 -0400, Tom Lane wrote:

Michael Fuhr <mike@fuhr.org> writes:

If anything I'd expect 2005-02-32 to be rejected as invalid, but I
don't know the history or rationale behind to_date's behavior.

It is rejected by the standard date input converter:

regression=# select '2005-02-32'::date;
ERROR: date/time field value out of range: "2005-02-32"
HINT: Perhaps you need a different "datestyle" setting.

However we consider that to_date() exists to be Oracle compatible,
and so I would regard this as a bug if and only if Oracle does
something different with the same input. Anyone know?

It's "almost" bug. And it's in TODO (but it's really long todo..:-( I
think to_date/timestamp() should be more pedantic.

Karel

PS. for volunteers for the work on new generation of to_char/date():
http://people.redhat.com/kzak/libfmt/libfmt-0.2-03262005.tar.gz

--
Karel Zak <zakkr@zf.jcu.cz>