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"
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
