Re: postgres date formatting? (SOLVED)

["Monique Y. Herman" <spam@bounceswoosh.org>]

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