Re: postgres date formatting?

[Paul Morgan <paulswm@earthlink.net>]

On Sun, 21 Dec 2003 20:34:28 -0700, Monique Y. Herman wrote:

> I know this isn't the postgres user group, but if anyone has any
> insight, I'd sure appreciate it.
> 
> Summary: attempting to use to_date on a table value results in a
> to_timestamp error.  What am I doing wrong?
> 
> I have a little database and want to prettyprint some dates on the web
> interface.
> 
> In /etc/postgres/postgres.conf , I have the following set:
> DATESTYLE = 'iso,european'
> 
> This is confirmed within psql:
> 
> ski=> show datestyle;
>            DateStyle
> -------------------------------
>  ISO with European conventions
> (1 row)
> 
> I have a table with a column of type 'date'.
> 
> ski=> select starting from visits;
>   starting
> ------------
>  2003-12-14
> etc ...
> 
> Here's what to_date gets me:
> 
> ski=> select to_date (starting, 'DD Mon YYYY') from visits ;
> ERROR:  to_timestamp(): bad value for MON/Mon/mon
> 
> Just for grins, I thought I'd try changing the datestyle:
> 
> ski=> set session datestyle = 'postgres,us';
> SET
> ski=> select starting from visits;
>   starting
> ------------
>  12-14-2003
> etc ...
> 
> But no matter what datestyle I use, I get that to_timestamp() error when
> trying to prettyprint my table.
> 
> For the record, I can use to_date in the canonical example just fine:
> 
> ski=> select to_date('05 Dec 2000', 'DD Mon YYYY');
>   to_date
> ------------
>  2000-12-05
> (1 row)

Monique, you're using the to_date function the wrong way round, which is
not an uncommon mistake.

to_date() converts a string to a date, *not* a date to a string.  So your
working example works because to_date() is taking the string '05 Dec
2000', using the format 'DD Mon YYYY' to determine the position of the
elements *within the string*, and then creating a date datatype, which it
is then displaying according to the default date display rules.

If you want to convert a date to a string (which *is* what you want to
do), the function is to_char().

Example:

I built a table 'test' with a date column 'test_dt'.  Here's the whole
example from inserting a date to getting it out formatted:

media=# show datestyle;
           DateStyle
-------------------------------
 ISO with European conventions

media=# insert into test values ('2003-12-14');
INSERT 17112 1

media=# select * from test;
  test_dt
------------
 2003-12-14
(1 row)

-- the wrong way to do it :

media=# select to_date (test_dt, 'DD Mon YYYY') from test;
ERROR:  to_timestamp(): bad value for MON/Mon/mon

-- the right way to do it:

media=# select to_char (test_dt,'DD Mon YYYY') from test;
  to_char
-----------
 14 Dec 2003
(1 row)

I am just so delighted that I can actually post a helpful note in this
group, even if it is OT :)

-- 
....................paul

Guy: "Would you like to dance?"
Girl: "I don't care for this song and surely wouldn't dance with you."
Guy: "I'm sorry, you must have misunderstood me, I said you look fat in
those pants"