Re: postgres date formatting?

[Pablo Aguiar <pabloaguiar@pop.com.br>]

On Sun, 21 Dec 2003 20:34:28 -0700
"Monique Y. Herman" <spam@bounceswoosh.org> wrote:

> Summary: attempting to use to_date on a table value results in a
> to_timestamp error.  What am I doing wrong?

You have to tell PostgreSQL the format of the string date you are
passing to to_timestamp (or to to_date) by parameter.

If you say 'DD Mon YYYY' by the 2nd param then postgre will expect
something like '22 Dez 2003' as the 1st param.

But according to your postgres.conf, when you select a date postgres
returns something in the format 'YYYY-MM-DD'. So change your formating
string to this.

Take a look:

scorphus=> insert into date_test values('2003-12-22');
INSERT 16979 1
scorphus=> select * from date_test;
    date
------------
 2003-12-22
(1 row)
 
scorphus=> select to_timestamp(date, 'DD Mon YYYY') from date_test;
ERROR:  to_timestamp(): bad value for MON/Mon/mon
scorphus=> select to_timestamp(date, 'YYYY-MM-DD') from date_test;
      to_timestamp
------------------------
 2003-12-22 00:00:00-02
(1 row)

Hope that helps.

Please excuse my English.

Regards,
Pablo Aguiar.