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


IMO to_date() should either recognize the date, even if shorter than the mask 
(Oracle compatible), or raise an error. Currently it gives completly wrong 
results, which is the worst option.

I tried to fix this myself, but I'm lost within backend/utils/adt/formatting.c


-- 
===================================================
 Mario Weilguni ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½               KPNQwest Austria GmbH
ï¿½Senior Engineer Web Solutions                         Nikolaiplatz 4
ï¿½tel: +43-316-813824 ï¿½ ï¿½ ï¿½ ï¿½                        8020 graz, austria
ï¿½fax: +43-316-813824-26 ï¿½ ï¿½ ï¿½              http://www.kpnqwest.at
ï¿½e-mail: mario.weilguni@kpnqwest.com
===================================================

Tue, 17 Apr 2001 19:46:19 +0200
Mario Weilguni <mweilguni@sime.com>
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@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Wed, 18 Apr 2001 10:47:04 +0200
Karel Zak <zakkr@zf.jcu.cz>
Re: Strange behaviour of to_date()
Am Mittwoch, 18. April 2001 10:47 schrieben Sie:
(...)
>
>  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)

Maybe it's not designed for my needs, but that does not change the fact that 
it's a bug. When the mask is not exact, it should raise an error, and not 
silently return WRONG values, which is really bad behaviour, and will result 
in "lost" data.


-- 
===================================================
 Mario Weilguni ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½ ï¿½               KPNQwest Austria GmbH
ï¿½Senior Engineer Web Solutions                         Nikolaiplatz 4
ï¿½tel: +43-316-813824 ï¿½ ï¿½ ï¿½ ï¿½                        8020 graz, austria
ï¿½fax: +43-316-813824-26 ï¿½ ï¿½ ï¿½              http://www.kpnqwest.at
ï¿½e-mail: mario.weilguni@kpnqwest.com
===================================================

Wed, 18 Apr 2001 18:12:50 +0200
Mario Weilguni <Mario.Weilguni@kpnqwest.com>
Re: Strange behaviour of to_date()