I'm a complete SQL noob. I know upgrading HSQL to use the DATEADD would be best, but that is way beyond my capabilities right now.
I have multiple tables containing DATE fields, and I need to increment them by one year *if* they fall after a given date. I'm the only one using the database, and I'm not overly concerned with leap years. I just need to change yyyy-mm-dd to yyyy+1-mm-dd if yyyy-mm-dd > x.
Help please (and thank you in advance).
Cap
Increment Year (HSQL 1.8)
Increment Year (HSQL 1.8)
OpenOffice 4.1.9 on Windows 10 Home
Re: Increment Year (HSQL 1.8)
Code: Select all
UPDATE "table" SET "date column" = Cast( Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") AS DATE)
See http://www.hsqldb.org/doc/1.8/guide/ch09.html build-in functions Year, Month, Day, Cast.
|| is the SQL concatenation operator like & in a spreadsheet.
Literal strings are in single quotes (double-quotes in a spreadsheet).
Names of tables and columns are in double-quotes.
We concatenate 3 numbers year+1, month, date with 2 dashes, so we get an ISO date string like 1999-12-31.
CAST(string AS DATE) converts the ISO date string into a date value.
The UPDATE command should be self explaining.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Increment Year (HSQL 1.8)
Thank you Villeory, this looks like what I need. Is there some way to target it so that it only affects dates after a given value? Like wrap it in the SQL equivalent of an if/then?
OpenOffice 4.1.9 on Windows 10 Home
Re: Increment Year (HSQL 1.8)
Why do I add links to the documentation? I stop spoon feeding at this point. If the HSQL documentation is too abstract, you find thousands of web pages with tutorials and explanations. SQL is more or less the same since > 40 years.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Increment Year (HSQL 1.8)
Sorry for being grumpy and impatient yesterday. You only need to append a WHERE clause to the UPDATE.
updates dates since 2021-01-01
By the way, you may also omit the Cast function in this particular context:
When you assign ISO strings to a date column or compare ISO strings with a date column the conversion is done automatically because the date context is given by the column type.
The Cast function is needed when you generate calculated dates as in:
The generated column "New Date" is a column of date values, Column "ISO" is a column of strings.
Code: Select all
UPDATE "table" SET "date column" = Cast( Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") AS DATE) WHERE "date column" >= '2021-01-01'
By the way, you may also omit the Cast function in this particular context:
Code: Select all
UPDATE "table" SET "date column" = Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") WHERE "date column" >= '2021-01-01'
The Cast function is needed when you generate calculated dates as in:
Code: Select all
SELECT "table".*,
Cast( Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") AS DATE) AS "New Date",
Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") AS "ISO" FROM "table"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Increment Year (HSQL 1.8)
Thank you Villeory, I appreciate that. I fiddled around for a while looking at WHERE yesterday, but wasn't sure I was on the right track. Looks like I was, but this will save me several syntax headaches I'm sure. My rudimentary PHP knowledge keeps mixing me up, since the syntax is similar but different.
OpenOffice 4.1.9 on Windows 10 Home
Re: Increment Year (HSQL 1.8)
Names of tables, columns and alias names (AS "name") in double-quotes.
Literal strings in single quotes.
|| is the concatenation operator.
Literal strings in single quotes.
|| is the concatenation operator.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice