From: Iuri Sampaio <iuri.sampaio@gmail.com>
To: pgsql-sql@postgresql.org
Subject: explicit casts
Date: Wed, 05 Jan 2011 22:24:34 -0200
Message-ID: <4D250BC2.2060207@gmail.com> (raw)
Hi there,
I installed postgresql 8.4 on my box and now i have troubles with the
following query regarding explicit casts.
select to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'YYYY')
as year,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as
num_days_in_month,
to_char(trunc(to_date(:the_date, 'yyyy-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day(to_date(:the_date, 'yyyy-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date(:the_date, 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1)),
'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date(:the_date, 'yyyy-mm-dd'), -1),
'fmMonth') as prev_month_name
from dual
the value assigned to the variable :the_date is '2010-01-05'
The error is
Error: Ns_PgExec: result status: 7 message: ERROR: function
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'YYYY-...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: select to_date(date_trunc('month',add_months( $1
,1)),'YYYY-MM-DD') - 1
CONTEXT: PL/pgSQL function "last_day" line 6 at SQL statement
how would i apply the following solution
date_trunc('month', p_date_in + interval '1 month')::date - 1
to fix the query above?
cheers,
iuri
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: iuri.sampaio@gmail.com
Subject: Re: explicit casts
In-Reply-To: <4D250BC2.2060207@gmail.com>
* 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