[Info-ingres] Doing arithmetic with ANSI dates

[Roy Hann]

Karl Schendel wrote:

>
>> On Oct 27, 2021, at 3:53 PM, Roy Hann <specially at processed.almost.meat> wrote:
>> 
>> I'm well used to doing arithmetic with the native Ingres DATE
>> (INGRESDATE) type, e.g.:
>> 
>>   SELECT birthdate + '3 months' FROM...
>> 
>> If birthdate is an ANSI date (ANSIDATE) the above still works
>> perfectly well.
>> 
>> But I doubt that is idiomatic ISO/ANSI SQL. I've Googled a bit and found
>> all kinds of syntax being used. I suspect ...+'3 months' or ...-'49
>> days' might not work reliably outside of Ingres.
>
> You'll want an INTERVAL.
>
> SELECT birthdate + INTERVAL '3' MONTH FROM ...

Aha. I had encountered that in my reading but it was not clear (till
now) that it is the standard syntax. Thank you.

For completeness, it seems like the permitted keywords are DAY, MONTH,
and YEAR only...right?

And while I am here, Ingres accepts ANSIDATE + INTEGER. The integer is
interpretted as a number of days. Does the standard endorse such an
expression?

Roy