Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread |
Messages 1 to 10 of 18 total |
LIB - func - IsLastMonth.sql |
Mon, Feb 6 2012 12:25 PM | Permanent Link |
Lucian | CREATE FUNCTION "IsLastMonth" (
IN "ADate" DATE) RETURNS BOOLEAN BEGIN DECLARE Result BOOLEAN DEFAULT False; DECLARE EndDate, StartDate DATE; DECLARE iYear, iMonth, iDay INTEGER; SET EndDate = CURRENT_DATE; SET iYear = EXTRACT(YEAR, EndDate); SET iMonth = EXTRACT(MONTH, EndDate); IF iMonth > 1 THEN SET iMonth = iMonth - 1; ELSE SET iYear = iYear - 1; SET iMonth = 12; END IF; CASE iMonth WHEN 1 THEN SET iDay = 31; WHEN 2 THEN IF IsLeapYear(iYear) THEN SET iDay = 29; ELSE SET iDay = 28; END IF; WHEN 3 THEN SET iDay = 31; WHEN 4 THEN SET iDay = 30; WHEN 5 THEN SET iDay = 31; WHEN 6 THEN SET iDay = 30; WHEN 7 THEN SET iDay = 31; WHEN 8 THEN SET iDay = 31; WHEN 9 THEN SET iDay = 30; WHEN 10 THEN SET iDay = 31; WHEN 11 THEN SET iDay = 30; WHEN 12 THEN SET iDay = 31; END CASE; SET EndDate = CAST(CAST(iYear AS VARCHAR) + '-' + CAST(iMonth AS VARCHAR) + '-' + CAST(iDay AS VARCHAR) AS DATE); SET StartDate = CAST(CAST(iYear AS VARCHAR) + '-' + CAST(iMonth AS VARCHAR) + '-01' AS DATE); IF ADate BETWEEN StartDate AND EndDate THEN SET Result = True; ELSE SET Result = False; END IF; RETURN Result; END DESCRIPTION 'Boolean function to return TRUE if the given date belongs to last month.' |
Wed, Feb 8 2012 3:31 AM | Permanent Link |
David Cornelius Cornelius Concepts | You should read up on the INTERVAL expression in EDB.
David Cornelius Cornelius Concepts |
Wed, Feb 8 2012 7:49 AM | Permanent Link |
Lucian | >You should read up on the INTERVAL expression in EDB.
I will, of course ... but come on, where's the optimized code ? Thanks, Lucian |
Wed, Feb 8 2012 10:06 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Lucian
(CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH Roy Lambert [Team Elevate] |
Wed, Feb 8 2012 10:25 AM | Permanent Link |
Lucian | >(CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH
Unbelievable I think I start to like INTERVAL thingees Thanks Lucian |
Wed, Feb 8 2012 11:33 PM | Permanent Link |
David Cornelius Cornelius Concepts | I was going to leave it as an exercise for the reader because it was getting
late and my brain was tired. But Roy came to the rescue! David Cornelius Cornelius Concepts |
Thu, Feb 9 2012 2:47 AM | Permanent Link |
Lucian | Guys,
Stuff that is not in the manual! I looked up a little on TFM and there are some questions that I have and the manual doesn't answer them. Perhaps for SQL gurus this is crystal clear, but for me isn't. So: The MONTH thingee seems to be, at least weird, if it is not buggy. If I add '1' MONTH to 2012-01-01 I expected what I got, 2012-02-01. If I add '1' MONTH to 2012-01-30 I did not expected what I got, 2012-03-01. It seems I started in January and I ended up in March, which calendaristically is kind of 2 months later. Also, if I subtract '1' MONTH from 2012-03-30, again, I did not expected what I got, 2012-03-01. It seems I started in Marhc and after taking out 1 month, I am still in March. List goes on, for example if you subtract '6' MONTH from 2012-03-31, you get 2011-10-01. I expected 2011-09-30. Probably this findings won't make the INTERVAL stuff less practical, but definitely it makes some stuff really weird and you have to take care how you use it. If some of the things you guys showed me here weren't that simple I would have most definetly sticked with Pascal code which is truly crystal clear and I don't think it would have raised any questions -) |
Thu, Feb 9 2012 3:40 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Lucian
"SQL guru" lets me out cos I ain't one. I don't use INTERVAL much mainly because I find it difficult to understand but I agree with your conclusions. You can either wait for Tim to come along and explain it or email him directly. I'll be fascinated by the answer, however, my take would be 2012-01-30 + 1 MONTH gives 2012-03-01: I would expect either this or 2012-02-29 (probably 2012-03-01 in this case) 012-03-30 - 1 MONTH gives 2012-03-01: I would expect either this or 2012-02-29 (probably 2012-02-29 here) 2012-03-31 - 6 MONTH gives 2011-10-01: I would expect either this or 2011-09-31 (I'm with you on this one) The logic seems to be: subtract the months; if the year, new month, and day do not yield a valid date add days to get the first valid date. I think the logic should be: subtract the months; if the year, new month, and day do not yield a valid date alter the day in the "direction of travel" to get the first valid date ie if adding months add a day if subtracting months subtract a day. Roy Lambert [Team Elevate] |
Thu, Feb 9 2012 4:18 AM | Permanent Link |
Lucian | >2012-03-31 - 6 MONTH gives 2011-10-01: I would expect either this or 2011-09-31 (I'm with you on this one)
Sep. only has 30 days, but I'm happy someone else agrees with me In my opinion MONTH should be treated semantically, not precise. Subtracting 1 month from some date, should give you some date in the PREVIOUS month. The same, adding 1 month to some date should give you some date in the NEXT month. That's my expectation. For this year, I expect Feb 29, when subtracting 1 month from March 29, 30 and 31. Period. regards Lucian |
Thu, Feb 9 2012 7:24 AM | Permanent Link |
John Hay | Roy Lambert
> (CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH How on earth did you work that out The left hand side does look like an odd construct. I would have expected this to return the number of days difference in a column named month. John |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |