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.'

You should read up on the INTERVAL expression in EDB.


David Cornelius
Cornelius Concepts

>You should read up on the INTERVAL expression in EDB.

I will, of course ... but come on, where's the optimized code ?

Thanks,
Lucian

Lucian


(CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH

Roy Lambert [Team Elevate]

>(CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH

Unbelievable
I think I start to like INTERVAL thingees

Thanks
Lucian

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

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 -)

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]

>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

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