Обсуждение: order by day or month, etc

Поиск
Список
Период
Сортировка

order by day or month, etc

От
"Leo Xavier"
Дата:
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




Re: order by day or month, etc

От
Jens Hartwig
Дата:
Hello Leo,

which version of PostgreSQL are you running? I tested:

select to_char(abm_dat, 'DD/MM/YYYY') AS new_date 
from t_dummy;

And it works fine:
 new_date
------------31/03/199215/06/199411/09/199319/11/1993...

Regards, Jens

Leo Xavier schrieb:
> [...]
> doing this:
> SELECT to_char(field, 'DD/MM/YYYY') AS new_date
> 
> as Francis Solomon said, didnt work... "unrecognized function" ...
> [...]

=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel.     : +49 (0)30 2554-3282
Fax      : +49 (0)30 2554-3187
Mobil    : +49 (0)170 167-2648
E-Mail   : jhartwig@debis.com
=============================================


RE: order by day or month, etc

От
"Francis Solomon"
Дата:
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@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Leo Xavier
> Sent: 03 January 2001 00:22
> To: pgsql-sql@postgresql.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
>
>
>