Re: subtracting from a date

Jay Vee <jvsrvcs(at)gmail(dot)com> writes:

> I tried that but get an error
> ? ? v_start_date date;
> ? ? v_minus_one_year date;
>
> I have v_start_date to start with and want to subtract one year and put into v_minus_one_year
> ??
> ? select v_start_date - interval '1 yr' as v_minus_one_year;

sj$ cat q
create or replace function foo(date)
returns date
as $$
declare
foo date;
begin
select into foo $1 - '1 year'::interval;
return foo;
end
$$
language plpgsql;

select foo(current_date);
sj$ psql -f q
SET
CREATE FUNCTION
foo
------------
2013-02-06
(1 row)

HTH
>
> --- ?I get:
>
> ERROR: ?query has no destination for result data
>
> On Thu, Feb 6, 2014 at 10:31 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
>
> On 02/06/2014 09:25 AM, Jay Vee wrote:
>
> I have reviewed working with dates documentation but some things are not
> clear and I cannot get an example to work for what I need.
>
> I am passing a date into a stored function like '2013-04-01'
>
> The stored function accepts this string as a date type.
>
> Within the function, I need to:
> 1. ?subtract one year from this date into another date type
> 2. ?subtract one month from this date into another date type
> 3. ?subtract one day from this date into another date type
>
> Are there any examples of this? ?This is not a timestamp type, but a
> date type.
>
> Something like this:
>
> test=> select '2013-04-01'::date - interval '1 yr';
> ? ? ? ?column?
> ---------------------
> ?2012-04-01 00:00:00
> (1 row)
>
> test=> select '2013-04-01'::date - interval '1 month';
> ? ? ? ?column?
> ---------------------
> ?2013-03-01 00:00:00
> (1 row)
>
> test=> select '2013-04-01'::date - interval '1 day';
> ? ? ? ?column?
> ---------------------
> ?2013-03-31 00:00:00
>
> You did say what language you are using for the function so the assignment will depend on that.
>
> thanks
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800