Subject RE: [IB-Architect] SQL Date Arithmetic
Author David Schnepper
First off, my memory of the date arithmetic is:

<date> + <numeric> ==> <date>
<date> - <numeric> ==> <date>
<numeric> + <date> ==> <date>
<numeric> - <date> ==> Error
<date> + <date> ==> Error
<date> - <date> ==> Numeric

Similar rules apply for <timestamp> and <time>.

As for the SQL semantics -- are you ready?

Add a new datatype, called <interval>.
<intervals> are either <hour to second> or <year to day> intervals,
with I think a hybrid type that
contains <year to second>.

<date> + <numeric> is, I think, offically unallowed. At best it is
supported but deprecated.
You need to do
<date> + <interval>
where to make the numeric into an interval is something like
cast( <numeric> as Interval(days to days) ).

There are all kinds of special rules.
(Adding 1 month & 1 day to Jan 28th for instance)
(And don't forget the special rules for leap seconds... <sigh>)

All the SQL stuff is from memory -- the spec goes into intense detail
about these types -- I don't
know if anyone has done the reference implementation of them.


> -----Original Message-----
> From: Jim Starkey [mailto:jas@...]
> Sent: Thursday, July 19, 2001 7:34 AM
> To:
> Subject: [IB-Architect] SQL Date Arithmetic
> Does anybody know what the official semantics of date
> arithmetic in SQL?
> The Interbase semantics are these:
> <date> + <whatever> => 'date' incremented by numeric value of
> 'whatever', interpreted as
> number of day,
> giving a date
> <date> - <whatever> => floating point number representing the
> number of days between 'date' and
> 'whatever' interpreted as a date.
> Oracle semantics are the above plus a second alternative for
> <date> - <whatever> => 'date' decremented by numerica value
> of 'whatever', interpreted
> as a date,
> giving a date.
> Among the problems with the Oracle semantics is that a date
> minus a string requires interpretation of the string to determine
> the actual operator and the type of the result.
> Perhaps our mole of the SQL Committee can shed some light on the
> official line.
> Jim Starkey
> To unsubscribe from this group, send an email to:
> Your use of Yahoo! Groups is subject to