using 'datediff()' it is only possible to produce results in years, months or days.
showing 10 years as 10 years, 119 months or 3650 days is not particularly meaningful.
some years ago i penned a piece of code using hsqldb 2.2 which displayed elapsed time in years, months & days.
that code has been used by others for several years without any negative feedback.
i responded to the forum user & supplied the code, it's available here>>> viewtopic.php?p=478413#p478413
anyway this got me thinking about making this functionality available to users of the default embedded db.
i have never seen any code penned by others who use hsqldb which shows elapsed time in years, months & days.
coding the hsqldb 2.x version was not too difficult thanks to the additional functions available but to do the same with hsqldb 1.8.0.10 was more of a challenge.
i believe i have succeeded & am presenting the result as an age calculator.
this is an example not a tutorial so i will give a brief overview rather than going into thorough detail.
the attachment is a simple db which contains:-
5 small tables.
1 form.
3 list box queries.
1 query 'qAgeCalc_Form' (data source for sub-form which also checks for data validity).
1 almost identical query without the integrity checks 'qAgeCalcFrom_tTestDates' to handle table data. this is the code to use within your own hsqldb 1.8.0.10 applications.
the main-form contains 3 list boxes (Day,Month,Year) with drop down menus.
the sub-form contains 2 text boxes (birth date,current age) & 1 button with refresh action.
it's always best to use the tab key when moving from one control to another.
the active list box will show a highlighted background.
you can use the keyboard, mouse wheel or up-down arrows to change values.
when the button is selected hitting the 'Enter' key saves the list box values to the filter table & refreshes the sub-form.
if you enter an invalid day (e.g. 31 September) then it will be modified as Sep 30.
currently the year is 2019 & the month is October.
if you enter an invalid month (e.g. 1 December 2019) then birthdate will be modified as current date.
it's impossible to enter an invalid year.
calculating the years & months was fairly easy it's simply down to methodical analysis.
calculating the days when day is <= current day is a simple subtraction (current day - day).
calculating the days when day is > current day is more difficult, the days will need to be wrapped.
remember that when you increase the day value by 1 then your age is reduced by 1 day, you are younger.
remember that when you decrease the day value by 1 then your age is increased by 1 day, you are older.
when the days in the 'Current Age' text box = 0 it's helpful if the user can view that 0 as the maximum number of days in the currently selected month for the currently selected year (February!!).
if day is then increased by 1 the number of days in the 'Current Age' text box = (maximum number of days in the currently selected month for the currently selected year) - 1.
it's easy to confuse yourself.
while working on this project i realised that my original hsqldb 2.2 effort contained a bug (now resolved).
i have duplicated this db using hsqldb 2.5.0. the code is simpler, smaller & more efficient than the hsqldb 1.8.0.10 version. it's a split db which requires a copy of the hsqldb.jar file & is therefore too large to upload.
please note i use windows 10 with all display options set to default. if you use a different operating system then the form may not appear exactly as is. because of the control types used i don't think that this will be an issue.
| Edit: 5 Nov 2019. deleted original attachment (faulty). uploaded fixed version. |