Re: SPL question
>From: Richard Harnden <richard.harnden@lineone.net>
>Reply-To: richard.harnden@lineone.net
>To: informix-list@iiug.org
>Subject: Re: SPL question
>Date: Tue, 18 Oct 2005 13:42:06 +0100
>
>Colin Dawson wrote:
> > I have an SP that takes a relative day and a time as input e.g.
> > ('-7','16:30:45') and am using the following code to set a variable to a
> > datetime string
> >
> > let p_cr_date = EXTEND(p_cr_day + p_add_days UNITS DAY, YEAR TO
> > SECOND)+INTERVAL(p_cr_time) HOUR TO SECOND;
>
>What are the types of p_cr_days, p_add_days and p_cr_time?
>
>Couldn't you just have posted the entire stored procedure?
No, I thought it too big to post
>
> >
> > This would expand to:- EXTEND(TODAY-7 UNITS DAY,YEAR TO
> > SECOND)+INTERVAL(16:30:45) HOUR TO SECOND
> >
> > If I exclude the INTERVAL part of the code it all works but if I include
>it
> > I get a syntax error.
> >
> > My question is, can I do this (or something similar) in SPL?
>
>Sure:
>
>CREATE PROCEDURE add_day_time(
> p_cr_date DATE,
> p_add_days INTERVAL DAY(3) TO DAY,
> p_cr_time INTERVAL HOUR(3) TO SECOND
>)
>RETURNING DATETIME YEAR TO SECOND;>
> RETURN EXTEND(p_cr_date, YEAR TO SECOND) + p_add_days + p_cr_time;
>
>END PROCEDURE;
>
>EXECUTE PROCEDURE add_day_time(TODAY, "-7", "16:30:45");
Problem was down to my use of INTERVAL, I removed the "INTERVAL(....) HOUR
TO SECOND" from the let statement so I just added a DATETIME to a DATETIME
and it compiled.
Thanks for help received.
Regards
Colin
There are 10 types of people in the world, those that understand binary and
those that don't
sending to informix-list