RE: order by day or month, etc

Hi Leo,

to_char() definitely works on my system (pg 7.02) so I'm not quite sure
whether you're using an older version or whether something else is
wrong. You can find more documentation on the function here:
http://www.postgresql.org/devel-corner/docs/postgres/functions-formattin
g.htm

Whether you can get it working or not, I'm not sure that doing text
formatting and then extracting month and year information from it is all
that good an idea. I think you would be better off using the functions
provided for manipulating dates and times rather than using that sort of
a hack. You might try doing something like this:

For a certain month/year:
SELECT field FROM table WHERE date_part('year', datefield)=1999 AND
date_part('month', datefield)=9;

For a date range:
SELECT field FROM table WHERE datefield BETWEEN '1999-09-01' AND
'1999-09-30';

Hope this helps.

Francis Solomon

> -----Original Message-----
> From: pgsql-sql-owner(at)postgresql(dot)org
> [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Leo Xavier
> Sent: 03 January 2001 00:22
> To: pgsql-sql(at)postgresql(dot)org
> Subject: [SQL] order by day or month, etc
>
>
> Hello
> first time I post something... good morning everyone!
> short presentation: Leo Xavier, Lisbon - Portugal, 17 years,
> my home-made
> site: www.megabenfica.com
> Sql7, win 2000...
>
> The question:
> How for example do I select all entrys from a certain month
> (of a certain
> year, of course) ?
> Or from a certain day? And how from a certain period, between
> day X and Y,
> i.e?
>
> The solution i found is to create three columns in the table:
> one with the
> day, a second with the month, a thir with the year... but
> this really is a
> little bit unprofessional ...
>
> doing this:
> SELECT to_char(field, 'DD/MM/YYYY') AS new_date
>
> as Francis Solomon said, didnt work... "unrecognized function" ...
>
> can anyone help me??
> Leo Xavier
>
>
>