Difference between 2 dates in LibreOffice Base (Years, Months, Days)

[mdiz777]

I just need to preface this to say that I am a complete noob with Macro’s, BASIC, and VBA programming, and I humbly apologize.

I am attempting to port a Microsoft Access database to LibreOffice Base as I will soon lose access to the Microsoft application. A while ago, I located a function in VBA code that I have working in MS Access, which is obviously not directly compatible with LibreOffice Base BASIC. I have been looking and reading for the past couple of weeks trying to do this myself, but I just do not understand and have been running into roadblocks every step of the way.

The Situation/Requirements

• I have 2 date fields (same table and same form), formatted as DD/MM/YYYY.
• The time difference between these date fields needs to be calculated in years, months and days (at this point, it doesn’t matter about 0 values).
• The resulting string (shown on the form in a read-only field) needs to be updated any time one of these dates change.

LibreOffice Information
Version: 7.6.7.2 (x86) / LibreOffice Community
Build ID: dd47e4b30cb7dab30588d6c79c651f218165e3c5
CPU threads: 6; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-AU (en_AU); UI: en-GB
Calc: CL threaded

For those that may be familiar with VBA, this is the working code:

Option Compare Database
Option Explicit

Function YMD(StartDate As Date, EndDate As Date) As String
Dim TotalMonths As Integer
Dim Years As Integer
Dim Months As Integer
Dim Days As Integer
Dim DaysDiff As Integer
Dim eomStart As Boolean, eomEnd As Boolean

TotalMonths = DateDiff("m", [StartDate], [EndDate])
DaysDiff = DatePart("d", [EndDate]) - DatePart("d", [StartDate])

'find out if StartDate is End of the Month
If DatePart("m", DateAdd("d", 1, StartDate)) = DatePart("m", DateAdd("m", 1, StartDate)) Then
    eomStart = True
Else
    eomStart = False
End If

'find out if EndDate is End of the Month
If DatePart("m", DateAdd("d", 1, EndDate)) = DatePart("m", DateAdd("m", 1, EndDate)) Then
    eomEnd = True
Else
    eomEnd = False
End If

Years = Int(TotalMonths / 12)
Months = TotalMonths - (Years * 12)
Days = DateDiff("d", DateAdd("m", TotalMonths, StartDate), EndDate + 1)

If DaysDiff = -1 Then
    If eomStart Xor eomEnd Then
        Months = Months - 1
        Days = DateDiff("d", DateAdd("m", TotalMonths - 1, StartDate), EndDate)
    Else
        Days = DateDiff("d", DateAdd("m", TotalMonths, StartDate), EndDate + 1)
    End If
ElseIf DaysDiff < -1 Then
    If eomStart And eomEnd Then
        Days = DateDiff("d", DateAdd("m", TotalMonths, StartDate), EndDate)
    Else
        Months = Months - 1
        Days = DateDiff("d", DateAdd("m", TotalMonths - 1, StartDate), EndDate + 1)
    End If
End If

YMD = (Years & "Y " & Months & "M " & Days & "D")
End Function

Thanks in advance for your attention and your patience. Any assistance would be very much appreciated.

[mikekaganski]

This code works, if you replace [StartDate] with StartDate, and [EndDate] with EndDate.

MsgBox YMD(#2023-12-30#, #2024-01-02#)

produces “0Y 0M 4D” (which is wrong: the correct difference is 3 days).

[Wanderer]

@mdiz777

The resulting string (shown on the form in a read-only field) needs to be updated any time one of these dates change.

Then I would not use a macro, but do this directly in a query, or a view in the database. (Will update, when a value is commited. You may need to reload the form.
.
Some problems to solve:

• SQL can vary and the available functions too. Especially the default HSQLDB 1.8 in Base is very limited.
• You have to check, what is stored in you database. Really dates or date-time values and
• you have to decide on needed precision. (Is Oct, 3rd 18:00 to Oct 4th 6:00 a day/ 0.5 days or 0 for “less than a day”)

As an example here a link to the chapter “date functions” for Sqlite, but you habe to look up your database.
https://www.sqlite.org/lang_datefunc.html

As a PS: I don’t recommend the embedded databases as working databases, if there is no special reason to use it.

[mdiz777]

Thanks so much @mikekaganski – I was hoping it was going to be something silly like that. However, while that works for a MsgBox and providing it with dates, I cannot get the field to pick up the dates already displayed on the form to display the difference value.

[mdiz777]

Again, thanks so much for the reply @Wanderer

Unfortunately, I am extremely limited with what resources I am able to use. Every time I think I may have found a solution around something, something else pops in the way to prohibit it.

This database for instance, is an “unauthorised” tool to aid my team and me to create a set document / file easier. We are using Excel mail merging into a Word document right now which is causing it’s own issues when the “rules” in the Excel file need amending over time–all the old files are then out of date for future reviews.

As this is “unauthorised”, and on a corporate network, I am needing to do this with solutions that do not need installing (my LibreOffice is the latest I can get as a portable application) or configuring. I’m fairly well versed in MySQL, but I cannot install it, nor would I be able to get it installed and configured on a server, or even locally, which would be the same for any other database software (SQL, Postgres etc.). SQLite would be great since it allows multi-user connections and is widely used, so information is easier to come by, but I cannot configure the DSN without administrative privileges.

This early test has two types of DB connections (one that I have at work, and one at home which I cannot share between the two locations so I’m having to do things twice): the embedded HSQLDB, and the (I’m guessing) embedded Firebird (available as another option when creating the database if that makes sense).

Answers to problems to solve:

• I know, and if you have any other solutions that do not require installation, and only configuring by way of files, I’m all ears
• What is stored, for today would be 04/10/2024 in the table as a DATE field
• Precision is determined as full days or date changes, in your example that would be classed as 1 day (as the date changed)

Hoping we can get further to the bottom of this… I don’t really want to scrap the idea.

[Wanderer]

@mdiz777

but I cannot configure the DSN without administrative privileges.

Actually there are UserDSN in ODBC,and if I remember this right you don’t need privileges for this, but for installing the odbc-driver you would need them, so this will not help you.

You may have a look at this extension instead. Not tested by me until now…

jdbcDriverOOo

[Wanderer]

@mdiz777

embedded Firebird

There are some date-functions, but I don’t see your requested format for date-difference

https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-functions-datetime.html

[Wanderer]

@mdiz777

Precision is determined as full days or date changes, in your example that would be classed as 1 day (as the date changed)

So only dates, no time. This makes it easier, but without a specific function builtin, there is a lot left to cover:

How to calculate age in T-SQL with years, months, and days

[RobertG]

And here a solution in SQL with internal Firebird:

SELECT "a"."StartDate", "a"."EndDate", "a"."y", "a"."m", 
DATEDIFF(day,DATEADD(month,"a"."m",DATEADD(year,"a"."y","a"."StartDate")),"a"."EndDate") AS "d"
FROM
(SELECT "StartDate", "EndDate", 
CASE WHEN DATEADD(year,DATEDIFF(year,"StartDate", "EndDate"),"StartDate") > "EndDate" 
THEN DATEDIFF(year,"StartDate", "EndDate") - 1 
ELSE DATEDIFF(year,"StartDate", "EndDate") END AS "y", 
CASE WHEN DATEADD(month,DATEDIFF(month,"StartDate", "EndDate"),"StartDate") < "EndDate" 
THEN MOD(DATEDIFF(month,"StartDate", "EndDate"),12) 
WHEN MOD(DATEDIFF(month,"StartDate", "EndDate"),12) = 0 
THEN 11 
ELSE MOD(DATEDIFF(month,"StartDate", "EndDate"),12) - 1 END AS "m" 
FROM "tbl_dates") AS "a"

It’s a little bit complicated because there are years with 365 and 366 days and month with 28, 29, 30 and 31 days.
Code requires a table “tbl_dates” with a field “StartDate” and a field “EndDate”.

[mdiz777]

I’ll comment from this reply, I did want to say thank you to everyone for the continued support and suggestions. It’s probably going to take a little while to investigate each of the possible solutions (long weekend here), I will be back to ask further or mark a solution.

I did want to ask a follow-up for @RobertG though: You mentioned a “tbl_dates” table, could you please elaborate on the filling of this table with examples of the StartDate and EndDate information required, and what your code is actually doing with that information?

[RobertG]

@mdiz777 : Here is a simple database with some example dates.
FB_Datediffs.odb (6.3 KB)

Created 3 queries.
First query is the query I posted. Will show the 2 dates and three columns (Years, Months and Days). Isn’t writable for new entries, because I created it depending on another query.
Second query will also show the field as you want with Years & "Y " & Months & "M " & Days & "D" in your macro code.
Third query will do the same, but will be editable, because all the content of the subquery has been moved in the query directly and the query contains the primary key. So you won’t need to fill a new field with the diff of years, months and dates. It will be done automatically when changing a data in a row. No macro needed.

[cpb]

@RobertG,
your code returns a number of inaccurate results.
.
@mdiz777,
if you are not willing to install a database then your options are HSQLDB embedded, Firebird embedded (which are both included with LibreOffice) or a split HSQLDB 2.x.
.
embedded databases carry the risk of sudden, fatal data corruption and can only be recommended for demos and examples.
.
you may find this link helpful:

Query to calculate Age in brackets when person is dead. (Libre Office Base)

Once again, I come here to ask for your help in building a query to calculate a person’s age. However, as the table includes living and deceased people, I would like the result of the formula to include in square brackets the age the person was when they died, to distinguish it from that of others who are still alive (current age). I managed to make the formula with square brackets, in Excel, although I didn’t get the result in red colour, as this would be the intended result for the Libre Off…

the original poster (OP) of that thread had specific requirements which the sql addressed but the fields “DOB” and “DOD” are equivalent to “StartDate” and “EndDate” respectively.
.
EDIT I forgot to upload the attachment.
I have used tuples from the OP’s table (where “DOD” is not null) for this HSQLDB embedded attachment.
DaysMonthsYears.odb (5.0 KB)
I have replaced “DOB” with “StartDate” and “DOD” with “EndDate”.
my code produces the exact same results as the AGE function in PostgeSQL i.e. AGE(dod,dob) :: text
.
the attachments result set:

RobertG’s result set using identical table source data:

[RobertG]

@cpb : You are right. Some of the wrong results got the reason in setting < "EndDate" instead of <= "EndDate". The other is: If I add months to a date I will get from 1950-02-28 with adding 11 month 1951-01-28. I thought adding month’s will work a little bit better and recognize the maximum days of a month.
Here the version with corrected “EndDate”.
FB_Datediffs.odb (6.2 KB)
Will have a look if it is possible to create a writable query, which also recognizes the right days of a month.
Edit: Have tried a little bit. Will get the same code as @cpb for getting the right value for days in february. Query in DaysMonthsYears could also be changed (by removing direct SQL execution) to an editable query. So you could input data in such a query without any problem. But be careful: Formatting of code will be lost by removing direct SQL executing and saving the query.

[Lupp]

Calculating with calendar data can easily go wrong.
Clever developers have provided the desired functions for spreadsheets.
However, these functions are also available in user code for any LibO component.
A very rough example with Draw can be found in the attachment.
disask111762CalcFunctionsUsedInNoncalcDocument.odg (23.2 KB)
I had a hunch and did another test.
In fact, the routine, which was already labeled as a “priory test”, was faulty.
I am now attaching a hopefully better replacement,
disask111762CalcFunctionsUsedInNoncalcDocument_fixed.odg (22.6 KB)

[skyandrews]

Greetings,
Here is my interpretation of the form you expressed in your initial post! I wrote a macro with comments to help keep your bearings!
no Table
DateDiff.odb (13.2 KB)
w/Table
DateDiffv2.odb (14.2 KB)