Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Doing Date INTERVAL Calculations in an EDB function |
Tue, May 6 2014 7:20 AM | Permanent Link |
Adam Brett Orixa Systems | 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?? |
Tue, May 6 2014 7:29 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Tue, May 6 2014 7:35 AM | Permanent Link |
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] |
Tue, May 6 2014 8:12 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Tue, May 6 2014 8:46 AM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Tue, May 6 2014 11:55 AM | Permanent Link |
Fernando Dias Team Elevate | 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] |
Wed, May 7 2014 3:37 AM | Permanent Link |
Roy Lambert NLH Associates 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 |
Wed, May 7 2014 11:34 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
It's not being picky, it's being exact The general idea remains, though. -- Fernando Dias [Team Elevate] |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |