[Solved] Subtracting Date fields

Discuss the database features

[Solved] Subtracting Date fields

Postby AlanE » Wed May 12, 2021 8:33 am

Hi, I'm a newbie, but can't find an answer to this question in Help or on the forum. I've created a database with 10 fields. I'll be entering data directly and don't need to use a form. I want to add/subtract one date field from another to generate a 'days between' number. In my Basefile, I will be entering a 'start date' and would like Base to automatically calculate and enter the 'end date' into an 'end date' field.

Example, Field Name is 'Invoice Date' which I enter manually. Another field is 'Payment Due Date' which I want Base to calculate as 'Invoice Date' + 10
In Calc, if I manually enter a date in cell B2 (the invoice date), then I could put the following formula in cell C2: +B2+10 and the output would be 10 days later than the date in B2.

My assumption is I would need to enter a 'default value' formula in the 'Payment Due Date' under Field Properties to do the calculation, but I can't figure out how to indicate the reference field/cell 'Invoice Date.'

Thank you in advance for your help
Last edited by AlanE on Thu May 13, 2021 11:43 pm, edited 2 times in total.
Open Office 7.1.3.2 (x64) on Windows 10 Pro, Firefox 88.0.1 (x64)
AlanE
 
Posts: 3
Joined: Wed May 12, 2021 8:03 am

Re: Subtracting Date fields

Postby Villeroy » Wed May 12, 2021 10:36 am

If the status bar of your database window looks like this:
Image
then you are woking with a HSQLDB of version 1.8. This is a rather outdated database engine and documented here: http://www.hsqldb.org/doc/1.8/guide/ch09.html
Under built-in functions you find the DATEDIFF function which calculates
DT1 - DT2 => N
SELECT * , Datediff('day', "DT1", "DT2" AS "Days" FROM "Table X" selects all the columns plus a column "Days" calculated from the difference in days between columns "DT1" and "DT2"
where DT1 and DT2 are date, time or timestamp values and N is a resulting number of days, months, years, hours, minutes or seconds.
This database engine does not include any DATEADD function to calculate D1 + N => D2
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: 30080
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Subtracting Date fields

Postby UnklDonald418 » Thu May 13, 2021 10:04 pm

I'll be entering data directly and don't need to use a form.

Generally, not a good plan. Forms give base much of it's power.
I will be entering a 'start date' and would like Base to automatically calculate and enter the 'end date' into an 'end date' field.

Storing a value like 'end date' that can be calculated at run time is a normalization violation and can lead to inconsistent data, i.e. if the 'start date' is changed the stored 'end date' will likely be wrong.

If you use a form for fields such as 'Invoice Date' then use a query like Villeroy recommended as the data source for a SubForm, you can display the correct 'Payment Due Date' even if 'Invoice Date' is changed.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.8 & LibreOffice 6.4.7.2 - Windows 10 Professional
UnklDonald418
Volunteer
 
Posts: 1405
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Subtracting Date fields

Postby AlanE » Thu May 13, 2021 11:46 pm

"Generally, not a good plan. Forms give base much of it's power." :knock:
Last edited by AlanE on Thu May 13, 2021 11:52 pm, edited 1 time in total.
Open Office 7.1.3.2 (x64) on Windows 10 Pro, Firefox 88.0.1 (x64)
AlanE
 
Posts: 3
Joined: Wed May 12, 2021 8:03 am

Re: Subtracting Date fields

Postby AlanE » Thu May 13, 2021 11:58 pm

Generally, not a good plan. Forms give base much of it's power.

:knock:
Open Office 7.1.3.2 (x64) on Windows 10 Pro, Firefox 88.0.1 (x64)
AlanE
 
Posts: 3
Joined: Wed May 12, 2021 8:03 am


Return to Base

Who is online

Users browsing this forum: No registered users and 3 guests