DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:subject:references
	:in-reply-to:content-type:content-transfer-encoding;
	b=mCY+3bnvEmDioWB1GMRxWNmBLiKXib0rceHP2FI//l4Ho/Wv6t8ultqg4QdLYPDUrs
	Cdx5cTISDwlLQGX6vSl9IqMtwv2sE2rJvfYQuhZqe9FQVSVW2LMdY1BwKTxqJXnTYzUi
	bdHjb1VC3rXFFsMR0eBns8Y8kTGNdGwMwHa44=
Message-ID: <4D251956.5050309@gmail.com>
Date: Wed, 05 Jan 2011 23:22:30 -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: Re: explicit casts
References: <4D250BC2.2060207@gmail.com>
In-Reply-To: <4D250BC2.2060207@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

So far,
I could write the following query

select to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'YYYY') as 
year,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'J') as 
first_julian_date_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
to_char(trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'Month'), 'D') as 
first_day_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as last_day,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1),'Day') as 
next_month,
trunc(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1),'Day') as 
prev_month,
trunc(to_date('2010-01-02', 'yyyy-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as 
days_in_last_month,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), 1), 'fmMonth') 
as next_month_name,
to_char(add_months(to_date('2010-01-02', 'yyyy-mm-dd'), -1), 'fmMonth') 
as prev_month_name
from dual


But i still miss some lines in order to properly explicit casts in the query

cheers,
iuri

On 01/05/2011 10:24 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