Increment Year (HSQL 1.8)

Discuss the database features

Increment Year (HSQL 1.8)

Postby CapFlint » Mon May 17, 2021 4:25 pm

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

Re: Increment Year (HSQL 1.8)

Postby Villeroy » Mon May 17, 2021 5:31 pm

Code: Select all   Expand viewCollapse view
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
User avatar
Villeroy
Volunteer
 
Posts: 29711
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Postby CapFlint » Mon May 17, 2021 7:37 pm

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

Re: Increment Year (HSQL 1.8)

Postby Villeroy » Mon May 17, 2021 8:18 pm

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: 29711
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Postby Villeroy » Tue May 18, 2021 12:58 pm

Sorry for being grumpy and impatient yesterday. You only need to append a WHERE clause to the UPDATE.
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
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   Expand viewCollapse view
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
User avatar
Villeroy
Volunteer
 
Posts: 29711
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increment Year (HSQL 1.8)

Postby CapFlint » Tue May 18, 2021 3:34 pm

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

Re: Increment Year (HSQL 1.8)

Postby Villeroy » Tue May 18, 2021 5:14 pm

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


Return to Base

Who is online

Users browsing this forum: No registered users and 4 guests