DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject
	:content-type:content-transfer-encoding;
	b=d7kEgCUzMiIQm1Y5lCphAChZrJ8mQW4HWrkngHFPI26cr05+Yzg0DnsvgyOB9WDSrU
	vjgIdXYMF8YlGjM1/MGaVD5rzq/VwlbqbP2JxpBNyjrHsXNtl+b+HJMvFF5ou/MDw6J6
	zR/fb5/tASy86ibIZSURGaBQajz5k3i3DKGIs=
Message-ID: <4D250BC2.2060207@gmail.com>
Date: Wed, 05 Jan 2011 22:24:34 -0200
From: Iuri Sampaio <iuri.sampaio@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.13) Gecko/20101208 Thunderbird/3.1.7
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: explicit casts
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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