pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
order by day or month, etc
3+ messages / 3 participants
[nested] [flat]

* order by day or month, etc
@ 2001-01-03 00:22 Leo Xavier <info@megabenfica.com>
  2001-01-03 07:30 ` Re: order by day or month, etc Jens Hartwig <jhartwig@debis.com>
  2001-01-03 18:04 ` RE: order by day or month, etc Francis Solomon <francis@stellison.co.uk>
  0 siblings, 2 replies; 3+ messages in thread

From: Leo Xavier @ 2001-01-03 00:22 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: order by day or month, etc
  2001-01-03 00:22 order by day or month, etc Leo Xavier <info@megabenfica.com>
@ 2001-01-03 07:30 ` Jens Hartwig <jhartwig@debis.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Jens Hartwig @ 2001-01-03 07:30 UTC (permalink / raw)
  To: Leo Xavier <info@megabenfica.com>; +Cc: pgsql-sql

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/1992
 15/06/1994
 11/09/1993
 19/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
=============================================



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: order by day or month, etc
  2001-01-03 00:22 order by day or month, etc Leo Xavier <info@megabenfica.com>
@ 2001-01-03 18:04 ` Francis Solomon <francis@stellison.co.uk>
  1 sibling, 0 replies; 3+ messages in thread

From: Francis Solomon @ 2001-01-03 18:04 UTC (permalink / raw)
  To: Leo Xavier <info@megabenfica.com>; pgsql-sql

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
>
>
>




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2001-01-03 18:04 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-03 00:22 order by day or month, etc Leo Xavier <info@megabenfica.com>
2001-01-03 07:30 ` Jens Hartwig <jhartwig@debis.com>
2001-01-03 18:04 ` Francis Solomon <francis@stellison.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox