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.
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 ...
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?
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?
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.
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.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 418 |
Nodes: | 16 (2 / 14) |
Uptime: | 21:52:34 |
Calls: | 8,764 |
Calls today: | 7 |
Files: | 13,287 |
D/L today: |
3 files (1,633K bytes) |
Messages: | 5,964,653 |