Re: explicit casts
От | Iuri Sampaio |
---|---|
Тема | Re: explicit casts |
Дата | |
Msg-id | 4D251956.5050309@gmail.com обсуждение исходный текст |
Ответ на | explicit casts (Iuri Sampaio <iuri.sampaio@gmail.com>) |
Ответы |
Re: explicit casts
(Adrian Klaver <adrian.klaver@gmail.com>)
|
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: