I want to do the following

--

CREATE FUNCTION "ReturnBestBeforeDate" (IN "aProductionDate" DATE, IN "aShelfLife" INTEGER)
RETURNS DATE
BEGIN
 DECLARE Result DATE;
                                           
SET Result = aProductionDate + INTERVAL CAST(aShelfLife as VARCHAR) DAY;
RETURN Result;
END

---

i.e. we have a shelf-life in days (i.e. 365) for a product, we are manufacturing the production on 1st May 2104, we want to calculate a best-before date for that product (1st May 2015 in my example).

I have tried a number of variations of this function unsuccessfully.

INTERVALS are always complicated ... but I really can't see how to make something like this work, even though it should be fairly easy.

EDB just says that the INTERVAL (aShelfLife in this example) must be a constant ... that a VAR cannot be used here.

How can I do date calculations like this??

Adam,

Yes, it's very easy.
Here it is:

CREATE FUNCTION "ReturnBestBeforeDate" (IN "aProductionDate" DATE, IN "aShelfLife" INTERVAL DAY)
RETURNS DATE
BEGIN
  RETURN aProductionDate + aShelfLife ;
END

--
Fernando Dias
[Team Elevate]

Adam,

An additional option:
If for some reason you want "aShelfLife" to be an INTEGER then:

CREATE FUNCTION "ReturnBestBeforeDate" (IN "aProductionDate" DATE, IN "aShelfLife" INTEGER)
RETURNS DATE
BEGIN
  RETURN aProductionDate + CAST(aShelfLife AS INTERVAL DAY) ;
END

--
Fernando Dias
[Team Elevate]

Fernando


Very nice. I couldn't picture how to enter the data so I had a quick play in EDBManager and you just have to enter the interval as an integer. I wasn't sure wethere it would need to be quoted or what but just a plain old integer works.

Roy Lambert

Roy & Fernando

Wow thanks guys. Intervals still mess me around all the time. CASTING straight to the correct data-type is obviously the right thing to do, once you see it done!

Just think of intervals as 'integers in a context'.
When you add 1 to a date, if it is just an integer, it could mean different things, depending on implementation.
With intervals the meaning is clear because if you add 1 to a date you have to tell if it is a month a day, a year, a minute or whatever. Neat, isn't it

--
Fernando Dias
[Team Elevate]

Fernando


>Just think of intervals as 'integers in a context'.
>When you add 1 to a date, if it is just an integer, it could mean different things, depending on implementation.
>With intervals the meaning is clear because if you add 1 to a date you have to tell if it is a month a day, a year, a minute or whatever. Neat, isn't it

Sorry to be picky but if I add 1 minute to a date its not a date its a timestamp.

ElevateDB does a silent CAST here which is good in some ways and bad in others.

Roy Lambert

Roy,

It's not being picky, it's being exact
The general idea remains, though.

--
Fernando Dias
[Team Elevate]