Andrew Jensen wrote:
> Hi Alberto,
>
> Juraj shows how you could update existing records with a new value.
>
> [...]
> The other thing is that very often it is not necessary to actually store the
> altered value. Instead it is common to store the base value and use
> calculated fields in queries to alter the data when it is read. Of course if
> you are wanting to do this in order to form a natural key of some sort, that
> doesn't help in the least.
>
> Drew
>
>
Hello Andrew,
This is the only way I know how to calculate date offsets in hsqldb's
_native_ SQL:
> SELECT
> Convert( Year("Date")+3 || '-' || Month("Date") || '-' || Day("Date"), DATE )
> AS "+3 years"
> FROM "Dates"
Extract the three numbers representing year, month and day, concatenate
them to ISO string "1999-12-31" and convert string to date.
If this is the only way at all, it _seems_ to work well with years and
months. It fails with _some_ day-offsets when the resulting day is in
next year.
> SELECT "Date",
> Convert( Year("Date") || '-' || Month("Date") || '-' || Day("Date")+99, DATE
> ) AS "+3 years"
> FROM "Dates"
1989-09-24 1990-01-01 +99 OK
1989-09-25 1990-01-02 +99 OK
1989-09-26 1990-01-03 +99 OK
1989-09-27 1990-01-04 +99 OK
1989-09-28 1990-01-05 +99 OK
1989-09-29 1990-01-06 +99 OK
1989-09-30 1990-01-07 +99 OK
1989-10-01 1989-10-10 +9 here it starts failing
1989-10-02 1989-10-10 +8
... weird sequence of offsets
1989-12-31 1989-12-13 -18
1990-01-01 1990-04-10 +99 OK, same year
... OK
I proved the calculated day offset in Calc and with DateDiff('dd',
"Date", "CalculatedDate").
Surprisingly, adding days across years works within the first millenium.
The smallest date we can enter is 100-01-01. Two digit years get
converted according to OOo's two-digit-date options.
I'm using Sun's OOo2.2 on Linux.
So the question is: How to calculate day-offsets in a reliable manner?
I hope that I could help Alberto Muller with a method for year-offsets,
which seem to work mostly. I found one issue with 3-digit years:
100-01-01 +3years --> 102-12-31 which is -1 day off, same with months.
Greetings,
Andreas Säger
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
