pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Francis Solomon <francis@stellison.co.uk>
To: Leo Xavier <info@megabenfica.com>
To: pgsql-sql@postgresql.org
Subject: RE: order by day or month, etc
Date: Wed, 3 Jan 2001 18:04:14 -0000
Message-ID: <NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk> (raw)
In-Reply-To: <001201c0751b$39405ca0$8902a8c0@centro.com>

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




view thread (3+ messages)

Message-ID: <NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk>
Permalink:  ../NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk/
Also on:    postgresql.org/message-id/NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: francis@stellison.co.uk, info@megabenfica.com
  Subject: Re: order by day or month, etc
  In-Reply-To: <NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk>
  References: <001201c0751b$39405ca0$8902a8c0@centro.com> <NEBBIFFPELJMCJAODNPKCEIHCEAA.francis@stellison.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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