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.

Dave

> -----Original Message-----
> From: Jim Starkey [mailto:jas@...]
> Sent: Thursday, July 19, 2001 7:34 AM
> To: IB-Architect@yahoogroups.com
> 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:
> IB-Architect-unsubscribe@onelist.com
>
>
>
> Your use of Yahoo! Groups is subject to

http://docs.yahoo.com/info/terms/