On Mon, 22 Dec 2003 at 21:30 GMT, Paul Morgan penned:
>
> 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 :)
>
Thank you so much! This is exactly what I was looking for, and your
example made it very easy to understand.
--
monique
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
