DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:reply-to:to:subject:date:user-agent:cc:references:in-reply-to
	:mime-version:content-type:content-transfer-encoding
	:content-disposition:message-id;
	b=VrgLcyrvdNM1+mlLgWNSrUCo9+VBcT/EBcttiTXDhHNa5pUxmOCT63E/SlrgWilqiB
	GkzzDJZchPKDx8IM7Y+YS0Hhu+tBwp/j43XPVXCQRMd6VAz9A6rMc3lXg99NEIm67W79
	tVpe4atpV/6bpI8xFUBUCHuATX5zkbG6G/KxI=
From: Adrian Klaver <adrian.klaver@gmail.com>
Reply-To: adrian.klaver@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: explicit casts
Date: Wed, 5 Jan 2011 18:20:49 -0800
User-Agent: KMail/1.9.10
Cc: Iuri Sampaio <iuri.sampaio@gmail.com>
References: <4D250BC2.2060207@gmail.com>
In-Reply-To: <4D250BC2.2060207@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201101051820.49646.adrian.klaver@gmail.com>

On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
> 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

If I am following this right the problem is in the last_day function and in 
particular the return value of the add_months function used in the 
date_trunc(). To be sure we would need to see those functions. As a shot in the 
dark:
 select to_date(date_trunc('month',add_months( $1 ,1)::date)...

-- 
Adrian Klaver
adrian.klaver@gmail.com