Instead of: interval "numDays day" use: (numDays * (interval '1 day')) Also, note that in your "select to_char...." you have misspelled (sp?) interval as "inteval". And last but not least, you can do simple day addition and subtraction with dates using integers. For example: > select '2005-12-26'::date + 8; returns '2006-01-03'. However, I'm not sure if that will work with 7.3.x. Good luck with your 8.x install! Jason ________________________________ From: pgsql-admin-owner@xxxxxxxxxxxxxx [mailto:pgsql-admin-owner@xxxxxxxxxxxxxx] On Behalf Of Chris Hoover Sent: Wednesday, December 28, 2005 8:49 AM To: pgsql-admin@xxxxxxxxxxxxxx Subject: [SPAM] - [ADMIN] Help with simple function - Bayesian Filter detected spam I need some help. I am trying to get a very simple function written to help our developers. Unfortunetly, the language we are developing in is giving us some grief over trying to do a "select to_char((current_date - inteval '1 day'),'yyyymmdd') into x". It is complaining about the use of the work interval. Anyway, I thought, no problem, I'll just create a date increment and date decrement set of functions. However, I can not get them to work. Can someone please point out what is wrong? CREATE OR REPLACE FUNCTION "public"."date_dec" (date, integer) RETURNS varchar AS' declare startDate alias for $1; numDays alias for $2; retDate varchar(8); begin select to_char((startDate - interval ''numDays day''),''yyyymmdd'') into retDate; return retDate; end; 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER; When I try to run this, it complains about numDays - "Error: Bad interval external representation 'numDays day'". Is there anyway around this? I am trying to keep the function generic so that our developers can use it to calculate any date range. Thanks for any help, Chris PG7.3.4 (8.1.1 upgrade happening next month)
