Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Extensions » View Thread |
Messages 11 to 18 of 18 total |
LIB - func - IsLastMonth.sql |
Thu, Feb 9 2012 7:49 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
>> (CURRENT_DATE - ADate) MONTH = INTERVAL '1' MONTH > >How on earth did you work that out Given my competence with SQL and INTERVALs ..... Wild guess <vbg> >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. It does doesn't it, and maybe it does. All I know is it works <mad giggle> Just testing it SELECT _DateAdded, (CURRENT_DATE - _DateAdded), (CURRENT_DATE - _DateAdded) MONTH, (CURRENT_DATE - _DateAdded) AS MONTH FROM Calls The first expression gives the number of days between the two, the second converts it into months (and it looks like its rounded up) and the third gives the number of days in a column called MONTH. I consider MONTH to be a keyword and would prefer ElevateDB not to allow this unless enclosed in double quotes. I think I'll go for a cold shower and a lay down. Roy Lambert |
Thu, Feb 9 2012 8:11 AM | Permanent Link |
Fernando Dias Team Elevate | Hi there,
<< 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. >> The standard says an exception should be raised, and Oracle does in fact raise an exception, when an invalid date is yield, but IMO in this case following the standard would be to completely defeat the potential of INTERVALs. I'd vote for Roy's idea, I think it’s the most logical and useful, as long as it is listed as a deviation from standard in the manual. Knock knock... hey Tim... are you there . -- Fernando Dias [Team Elevate] |
Thu, Feb 9 2012 9:19 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Fernando
>The standard says an exception should be raised, and Oracle does in fact >raise an exception, when an invalid date is yield, but IMO in this case >following the standard would be to completely defeat the potential of >INTERVALs. Do you ever think the people responsible for the standard were on some other planet, or had access to some interesting smoking materials? Roy Lambert |
Fri, Feb 10 2012 7:01 AM | Permanent Link |
Fernando Dias Team Elevate | Roy,
<<Do you ever think the people responsible for the standard were on some other planet, or had access to some interesting smoking materials?>> Yeah, and sometimes I feel it would help me too when I try to read the standard specifications -- Fernando Dias [Team Elevate] |
Tue, Feb 21 2012 7:59 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Lucian,
<< 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. >> The problem is that you're mixing a year-month interval with a date. There are day-time intervals, and month-year intervals, and the two do not mix well together in a lot of cases because you're dealing with two completely different units of measure. Dates implicitly use a day-time interval unit of measure because they are, at their core, a measure of the number of days. When adding months to a date, what EDB does is simply increment the literal month, and then make sure that the resulting date is valid for representational purposes by incrementing the month if the day number is greater than the valid number of days in the month. I'll have to check this out further, but I may be able to make this work more like what you're wanting (stay within the month). The issue, though, is that the routines that deal with this type of thing are very generic, and I don't think they have any contextual information regarding the fact that one is adding months, etc. -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Feb 21 2012 8:03 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | John,
<< 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. >> It's a special construct used when subtracting dates/times that produce intervals: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Arithmetic_Operators under "Date, Time, and Timestamp Subtraction". -- Tim Young Elevate Software www.elevatesoft.com |
Tue, Feb 28 2012 1:54 PM | Permanent Link |
Lucian | >The problem is that you're mixing a year-month interval with a date.
Ok, I understand now. Than I think it seems more logical to not allow the mix. I tried adding a computed column where the computation would be adding a string with a number. I got error 700, etc. That's what should happen if types don't mix. Best regards, Lucian |
Wed, Feb 29 2012 2:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Lucian,
<< Ok, I understand now. Than I think it seems more logical to not allow the mix. >> Sorry for not being clear - I was actually just pointing out what was causing the issue, in general, not really stating that you were doing something wrong. This is fixed in 2.08: http://www.elevatesoft.com/incident?action=viewaddr&category=edb&release=2.08&incident=3547 -- Tim Young Elevate Software www.elevatesoft.com |
« Previous Page | Page 2 of 2 | |
Jump to Page: 1 2 |
This web page was last updated on Friday, September 20, 2024 at 05:39 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |