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

English
,
#1

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.

#2

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).

#3

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.

#4

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.

#5

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.

#6

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…

#7

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

#8

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

#9

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”.

#10

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?

#11

@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.

#13

@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:

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:
HSQLDB_embedded

RobertG’s result set using identical table source data:
RobertG

#14

@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.

#16

I wanted to just reply here to apologise for lack of response. I went scouring through documentation on company policy the following week to asking this, and found that it was all null and void, due to all my possible workarounds conflicting with what I would be allowed to do on the computer network. I have therefore “abandoned” this solution, and marked the solution that most closely resembled what would have been the final product. Thank you all for you help and support on this issue–most were viable solutions IF I were able to continue. At the moment, I’m just going to have to stick to the resources I have openly available to me.

#17

Have changed the solution, because @cpb got the solution, which gives the right result. What I have written ignores difference in February.

#12

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)

#18

The question was, how to make a whole column of calculated values part of a “formula result”, if you look at a query as a “database formula” returning a 2D-Array. If the underlying database engine has enough built-in functions, this is fairly easy. If not, it is rather difficult, and no macro language helps to calculate parts of the query result.

#19

The question contained bad (VBA) Basic code, and I showed how to get concise and correctly working code for the purpose in LibreOffice. Doing so I stressed that Calc functions can be called by user code independent of the type of the active document.
If explicitly requested I would also show how this can be done if the arguments and the required result are arrays (“iterative” array-evaluation).
I did and do not tell anything about the question if or how results obtained by user code can be merged with/into results returned on SQL statements using any database engine with any SQL flavor or any extension.

1 Like
#15

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)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.