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

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]

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

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]

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

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

>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

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