Increment Year (HSQL 1.8)

Discuss the database features
Post Reply
CapFlint
Posts: 3
Joined: Mon May 17, 2021 4:17 pm

Increment Year (HSQL 1.8)

Post by CapFlint »

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
OpenOffice 4.1.9 on Windows 10 Home
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Post by Villeroy »

Code: Select all

UPDATE "table" SET "date column" = Cast( Year("date column") +1 || '-' || Month("date column") || '-' || Day("date column") AS DATE)
29th February becomes 1st of March.

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
CapFlint
Posts: 3
Joined: Mon May 17, 2021 4:17 pm

Re: Increment Year (HSQL 1.8)

Post by CapFlint »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Post by Villeroy »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Post by Villeroy »

Sorry for being grumpy and impatient yesterday. You only need to append a WHERE clause to the UPDATE.

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'
updates dates since 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'
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:

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"
The generated column "New Date" is a column of date values, Column "ISO" is a column of strings.
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
CapFlint
Posts: 3
Joined: Mon May 17, 2021 4:17 pm

Re: Increment Year (HSQL 1.8)

Post by CapFlint »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Post by Villeroy »

Names of tables, columns and alias names (AS "name") in double-quotes.
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
Post Reply