Re: Strange behaviour of to_date()

On Tue, Apr 17, 2001 at 07:46:19PM +0200, Mario Weilguni wrote:
> I noticed a quite strange behaviour of to_char() in 7.0 and 7.1. It treats
> abbreveated forms of a date completely wrong. Example:
>
> -- this one is ok
> mario=# select to_date('04.01.2001', 'dd.mm.yyyy');
> to_date
> ------------
> 2001-01-04
>
> -- this is completly wrong, but NO error raised
> mario=# select to_date('4.01.2001', 'dd.mm.yyyy');
> to_date
> ------------
> 0001-01-04
>
> -- completly wrong as well
> mario=# select to_date('4.1.2001', 'dd.mm.yyyy');
> to_date
> ------------
> 0001-01-04

Really bug? What you obtain from 'dd.mm.yyyy' in to_char()

test=# select to_char('04.01.2001'::date, 'dd.mm.yyyy');
to_char
------------
04.01.2001
(1 row)

'04.01.2001' and '4.1.2001' are *different* strings with *different*
format masks....

See (and read docs):

test=# select to_char('04.01.2001'::date, 'FMdd.FMmm.yyyy');
to_char
----------
4.1.2001
(1 row)

test=# select to_date('4.1.2001', 'FMdd.FMmm.yyyy');
to_date
------------
2001-01-04
(1 row)

Yes, Oracle support using not exact format mask, but Oracle's to_date
is very based on date/time and not support others things:

SVRMGR> select to_date('333.222.4.1.2001', '333.222.FMdd.FMmm.yyyy') from
dual;
TO_DATE('
---------
ORA-01821: date format not recognized

test=# select to_date('333.222.4.1.2001', '333.222.FMdd.FMmm.yyyy');
to_date
------------
2001-01-04
(1 row)

or nice:

test=# select to_date('33304333.1.2001', '333dd333.FMmm.yyyy');
to_date
------------
2001-01-04
(1 row)

And primarily Oracle's to_date() is designed for operation that in
PG is solved via timestamp/date cast. For example you can use in
Oracle to_date('4.1.2001') without format mask and it's same thing
as 4.1.2001::date cast('4.1.2001' as date) in PG.

The to_char()/to_date() works as say docs :-)

Better support for not exact masks is in my TODO fo 7.2.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz