On Oct 27, 2021, at 3:53 PM, Roy Hann <specially@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 ...

Karl

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Karl Schendel wrote:

On Oct 27, 2021, at 3:53 PM, Roy Hann <specially@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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Oct 28, 2021, at 4:08 AM, Roy Hann <specially@processed.almost.meat> wrote:

Karl Schendel wrote:

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?

It's a bit more complicated than that. I don't have the full syntax at hand, but
the options are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
YEAR TO MONTH, and DAY TO HOUR, MINUTE, or SECOND.
Intervals can have either year and month fields, or day / hour / minute / second
fields, but not both for some bizarre reason that I don't know.

The quoted part in the middle is 'year-month' for a year to month interval,
and 'day hour[:min[:second]]' for a day to second interval. You can have
a leading minus sign (inside the quotes) to indicate a negative interval.

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?

I don't think it does.

Karl

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Karl Schendel wrote:

On Oct 28, 2021, at 4:08 AM, Roy Hann <specially@processed.almost.meat> wrote:
[snip]
For completeness, it seems like the permitted keywords are DAY, MONTH,
and YEAR only...right?

It's a bit more complicated than that. I don't have the full syntax at hand, but
the options are YEAR, MONTH, DAY, HOUR, MINUTE, SECOND,
YEAR TO MONTH, and DAY TO HOUR, MINUTE, or SECOND.
Intervals can have either year and month fields, or day / hour / minute / second
fields, but not both for some bizarre reason that I don't know.

I recall an argument that it is because months don't all have the same
number of days so if an expression included months as well as days you
can't legitimately evaluate it. Sometimes, depending in my mood I can
hypnotize myself into agreeing.

The quoted part in the middle is 'year-month' for a year to month interval, and 'day hour[:min[:second]]' for a day to second interval. You can have
a leading minus sign (inside the quotes) to indicate a negative interval.

I had wondered why it has to be quoted. 'YEAR-MONTH' explains it. It's
still kinda grotesque-looking and it grates, but OK, fine.

I don't see why it can't be an expression that evaluates to a string
though.

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?

I don't think it does.

On the one hand that's a pity, because it's unambiguous and useful. On
the other hand Ingres/X/Vector support it so I'm happy.

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)