Datedif(f) in queries and reports

[GEM2]

Beginner’s question - Using Base I am using the following fields in query/report to create a list of family birthdays giving their current ages whenever the list is printed:
Query: DATEDIFF( ‘yy’, [Date of Birth], [CURDATE()] )
Report: DATEDIF([Date of Birth];TODAY();“y”)
I want to display the ages in years. They work fine unless the Birthday is later in the year than when the query or report is being run. e.g. Today is 9th November 2020 - somebody with a birthday of 25 November 1951 is shown as being 69 when it should be 68. It is obviously just subtracting the years and not accounting for the date and month. What am I doing wrong ?

Thanks for your help!

[Ratslinger]

Hello,

First, SQL used can be different depending upon the database being used. Base currently has HSQLDB embedded and Firebird embedded available and you can connect to many, many others. Always specify what you are using and the OS and LO version you are using. This comes into play more often than you may imagine.

For calculating ages it is not as simple as you have it. For fairly accurate age see the answers in these posts:

[SOLVED] FireBird, Libreoffice Base DATEDIFF

Good Morning, Please, could you give me an example of DATEDIFF, in LIBREOFFICE BASE with FIREBIRD? HOW WOULD YOU LIKE A QUESTION IN LIBREOFFICE BASE FIREBIRD to know the current age? Attachment example filedatediff.odb

Age Query - Additional Columns

I am using a bit of SQL from this thread: Calculate Age From Birthday to calculate the age in a query. It is working perfectly to calculate the age but I am having trouble adding additional columns to that query. I would like to at least add the “ID” column from the same table. SELECT (1.0 * MYDATE / 365) AS AGE FROM (SELECT DATEDIFF( 'day', "BIRTHDATE", CURRENT_TIMESTAMP ) "MYDATE" FROM "BIRTHDATES") In my basic knowledge of SQL I usually simply add a comma and the name of the next column tha…

Can one calculate age from a birthday field in Libre Office Base?

The question I am asking really refers to any date. Can one calculate time (either in days or years) between a past date (in a date field) and today. I know this can easily be done in Libre Office Calc, but I am wondering if it can be done in Libre Office Base. I am not familar with sql, so I was wondering if this could be done in a design query or through a wizard. I am also interested in knowing how to calculate time between two date fields, and two hour/minute fields (in minutes). I am cu…

These are just some presented methods.

Also, you are better off not doing this in Report Builder but rather through SQL (query) and base the Report on that.

Edit:

Did quick test in Report builder. This works:

DATEDIF("2019-02-01";TODAY();"d") / 365.25

so this should work for you:

DATEDIF([Date of Birth];TODAY();"d") / 365.25

as long as the field is formatted correctly.

Another note, using spaces in field, table or file names will cause you headaches sooner or later. Avoid them, and instead use something like:

DateOfBirth
Date_of_Birth
date_of_birth
DOB
dob
birth_date         (my preference)

[GEM2]

Hi Ratslinger
Thanks for your comments. I am using LO 6.4.6.2 and macOS Catalina 10.15.7. I’ve made a note about spaces in the Field names for the future. The field is formatted as yyyy-mm-dd

Your solution works and I now get a list of ages of e.g.
John. 20.43
Dave. 50.57 etc - thank you

However I would ideally like the Numbers to be whole integers of Current age. So somebody born in 12/1/1951 is shown as 69 and not 70. At the moment it appears as 69.83 but if I reduce to zero decimal points it rounds up to 70. Is it possible for it to show 69 (which is the actual age) ? I have looked at the references you gave but I can’t seem to find a solution?

Thanks for your help

[Ratslinger]

@GEM2,

Thank you for further information but you never stated the database you are using - the most important item.

For HSQLDB Query:

CAST(DATEDIFF( 'day', "Date of Birth", CURRENT_DATE ) / 365.25 AS INTEGER)

For Report Builder:

INT(DATEDIF("2019-02-01";TODAY();"d")/365.25)

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.

[GEM2]

Maybe a solution ?/ workaround ?
DATEDIF([Date of Birth];TODAY(); “d”) / 365 - 0.5. seems to work if rounded to zero decimal points ?

[Ratslinger]

@GEM2,

Don’t understand. What workaround? Gave you a tested answer in the comment above. 365 is not valid but 365.25 is. Your solution will generate incorrect results with other dates.

Based upon HSQLDB - you insist on not providing what database you are using!

[GEM2]

Yes that works really well - Many thanks

[GEM2]

Using HSQLDB. I didn’t refresh the page so only saw your post after I had posted mine. Mine should have read - DATEDIF([Date of Birth];TODAY(); “d”) / 365.25 - 0.5
It does actually work when reduced to zero decimal points. But it’s only a workaround - yours is a better solution. Many thanks.

[Ratslinger]

Yes, your alternative does in fact work.