[Solved] Increase Dates by 1 year

Creating tables and queries
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] Increase Dates by 1 year

Post by Nocton »

I have a table of Matches played with a date field 'DatePlayed'. I wish to copy the matches from one year, say 2017, to a new year, say 2018. The copying is easily done, but how can I increment all dates by one year?
I want something like the DATEADD function which OO SQL does not support -- DATEADD(year, 1, [DatePlayed]).

Perhaps I have to take the date apart into day, month and year and then reassemble it? But I hope there is a simpler solution.
Last edited by Nocton on Mon Feb 05, 2018 4:20 pm, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Increase Dates by 1 year

Post by RoryOF »

2018 not being a leap year, why not add 365 days?

I've just tested this, and it works.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increase Dates by 1 year

Post by Villeroy »

Code: Select all

=DATE( YEAR(A1)+1 ; MONTH(A1) ; DAY(A1)
https://wiki.openoffice.org/wiki/Docume ... E_function
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Increase Dates by 1 year

Post by Nocton »

Thank you, RoryOF, that would be accurate enough even in a leap year. But what is the syntax?

Villeroy, your solution is for Calc not Base SQL.
OpenOffice 4.1.12 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Increase Dates by 1 year

Post by RoryOF »

I only tried in Calc, where I had dates in column A; in column B, also formatted as dates, I entered =A1+365 and got the correct result. For Base tables I cannot help.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increase Dates by 1 year

Post by Villeroy »

Sorry, wrong forum. Switch to HSQL2 or any other database engine that provides DATEADD. However, the increase by one year is trivial even without that function. Just concatenate the calculated year, month, day to ISO date string and convert the ISO string:

Code: Select all

SELECT CAST(YEAR("D") ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off" FROM "Somewhere"
"D" is a date column, "Somewhere" is a record set. The trouble begins when you try to convert calculated months and days. CAST('2018-45-99' AS DATE) fails.

Switch to HSQL2 with a few clicks: viewtopic.php?f=21&t=86071
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Increase Dates by 1 year

Post by Nocton »

Thank you, Villeroy. I may convert to using HSQL2, but will have to do so for other users too.

As I am going to run from a form, I may well use the DateAdd function in a Basic macro.

I ran the CAST statement. It was OK but output was not in a 'normal'/recognisable date format such as 03-02-18, but a 5-digit number.
OpenOffice 4.1.12 on Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Increase Dates by 1 year

Post by Sliderule »

Nocton wrote:Thank you, Villeroy. I may convert to using HSQL2, but will have to do so for other users too.

As I am going to run from a form, I may well use the DateAdd function in a Basic macro.

I ran the CAST statement. It was OK but output was not in a 'normal'/recognisable date format such as 03-02-18, but a 5-digit number.
There are two answers to help you - assuming your are using the Embedded Database ( HSQL 1.8.0.10 ), but, I would strongly recommend you upgrade to HSQL 2.4 . . . so . . . you can use the DATEADD function.

If we assume your Query looked like below:

Code: Select all

SELECT 
   CAST(YEAR("D") + 1 ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off" 
FROM "Somewhere"
  1. Run the Query in direct mode, withOUT the Base Parser touching it
  2. If you want the Base Parser, for example, to use a Parameter Prompt, code / save as below:

    Code: Select all

    Select A.* From (
       SELECT 
          CAST(YEAR("D") + 1 ||'-'|| MONTH("D") ||'-'|| DAY("D") AS DATE) AS "One Year Off" 
       FROM "Somewhere") as A
Explanation: Above Query adds:
  1. Select A.* From ( on first line.
  2. At the end of the Query is an additional ) as A
Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Increase Dates by 1 year

Post by Villeroy »

Nocton wrote:Thank you, Villeroy. I may convert to using HSQL2, but will have to do so for other users too.
If you are talking about one multi-user database, you have to separate the database from the database document anyway. First of all, the embedded database is good enough for demos but very prone to data loss. This serious problem goes away when you connect a Base document to a stand-alone database.
After you extracted your database and upgrade to a recent HSQL driver you can run HSQL as a database server and use the same database simultaniously from many work places. All this can not be done with embedded databases.
If you are talking about many single-user databases, you can simply wrap the database, the Base document and any stand-alone forms, reports, spreadsheet, third-party tools in a zip file and add my auto-install macro to the Base document.
Install my little macro suite FreeHSQLDB v.0.3. Copy 2 of its modules to your Base document which is connected to a stand-alone HSQL, adjust the constants in the auto-install module and assign the document open event before you zip the whole package.
As I am going to run from a form, I may well use the DateAdd function in a Basic macro.
There is no DATEADD in StarBasic. You would have to write your own function or write a macro in a mature programming language such as Python or Java. A macro would be the most complicated and isulated solution.
I ran the CAST statement. It was OK but output was not in a 'normal'/recognisable date format such as 03-02-18, but a 5-digit number.
The 5 digit integer is the correct value for a form control, table cell, spreadsheet cell or database field. Just apply some date format to your liking. The office suite internally handles dates as integer day numbers and sometimes calculated dates show their "true" value.
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
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: Increase Dates by 1 year

Post by Nocton »

Thank you SlideRule and Villeroy.

First of all I am running in File, not embedded, mode with the hsqldb.jar file loaded at the start using macro from this forum.
I'll look into changing to HSQL 2.4.

Running the Query in direct mode gave the dates in the standard format, so that's fixed that problem.
There is no DATEADD in StarBasic.
That maybe, but there is in the Basic that is supplied with OpenOffice and it does the job I want.
OpenOffice 4.1.12 on Windows 10
Post Reply