Populate a calendar table

Raymond O'Donnell wrote:

� � �to_char(extract (month from d.dates), 'FMmonth') as month_name,
� � �to_char(extract (day from d.dates), 'FMday') as day_name

These formatting patterns are invalid. Check the documentation for the
real ones.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

"Raymond O'Donnell" <rod@iol.ie> writes:

to_char(extract (month from d.dates), 'FMmonth') as month_name,
to_char(extract (day from d.dates), 'FMday') as day_name

I think you want just to_char(d.dates, 'FMmonth') and so on.
What you're invoking above is to_char(numeric) which has entirely
different format codes...

regards, tom lane

Raymond O'Donnell escreveu:

'Lo all,

I've created a calendar table based on an article I found on the web,
but I can't figure out what's wrong with the query I've written to
populate it. Here's the table -

CREATE TABLE aux_dates
(
the_date date NOT NULL,
the_year smallint NOT NULL,
the_month smallint NOT NULL,
the_day smallint NOT NULL,
month_name character varying(12),
day_name character varying(12),
CONSTRAINT aux_dates_pkey PRIMARY KEY (the_date)
)

- and here's what I've come up with to populate it -

insert into aux_dates
select * from (
select
d.dates as the_date,
extract (year from d.dates) as the_year,
extract (month from d.dates) as the_month,
extract (day from d.dates) as the_day,
to_char(extract (month from d.dates), 'FMmonth') as month_name,
to_char(extract (day from d.dates), 'FMday') as day_name
from
(
select ('2007-01-01'::date + s.a) as dates
from generate_series(0, 14) as s(a)
) d
) dd;

The error I get is:

ERROR: "." is not a number
SQL state: 22P02

Any help will be appreciated!

Try:
to_char(d.dates, 'FMmonth') as month_name,
to_char(d.dates, 'FMday') as day_name

[]s
Osvaldo

_______________________________________________________
Yahoo! Mail - Sempre a melhor op��o para voc�!
Experimente j� e veja as novidades.
http://br.yahoo.com/mailbeta/tudonovo/

On 27/03/2007 17:00, Tom Lane wrote:

I think you want just to_char(d.dates, 'FMmonth') and so on.
What you're invoking above is to_char(numeric) which has entirely
different format codes...

Duh! Of course.....I didn't spot that.

Thanks to all who replied.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------