Re: BUG #1518: Conversions to (undocumented) SQL year-month

On Sat, 19 Mar 2005, Roy Badami wrote:

> Roy> I don't have a copy of the spec, but according to "A guide to
> Roy> the SQL standard" conversions like this that would discard
> Roy> data are supposed to raise an exception.
>
> Just to clarify, my understanding is that in ANSI SQL it is valid to
> convert from the data type INTERVAL YEAR TO MONTH to the data type
> INTERVAL YEAR, but the conversion should raise an exception if the
> value is not an integral number of years...

Hmm, I'm not entirely sure what the spec says about this. I think the
covering clause in SQL92 is 6.10 (<cast specification>) GR 12d. The error
definition appears to be:

d) If SD is interval and TD and SD have different interval pre-
cisions, then let Q be the least significant <datetime field>
of TD.
i) Let Y be the result of converting SV to a scalar in units Q
according to the natural rules for intervals as defined in the
Gregorian calendar.

ii) Normalize Y to conform to the datetime qualifier "P TO Q"
of TD. If this would result in loss of precision of the
leading datetime field of Y, then an exception condition is
raised: data exception-interval field overflow.

And SQL 99 seems to add a sentence saying "whether to truncate or round in
the least significant field of the result is implementation-defined."