[SOLVED] FireBird, Libreoffice Base DATEDIFF

[marcosAoo]

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

[Ratslinger]

@marcosAoo,

When cross posting, please note that this is done and where - also posted on → [Solved] FireBird, Libreoffice Base DATEDIFF (View topic) • Apache OpenOffice Community Forum

This can save efforts!

[mariosv]

And here [RESUELTO] FireBird, Libreoffice Base DATEDIFF (Ver Tema) • Apache OpenOffice: Foro oficial de la comunidad

[Ratslinger]

Hello,

You have actually asked two questions here with one being harder than the other.

My answer here presents syntax for some Firebird functions and has a link to the Firebird documentation → Firebird Migration Expectations.

Here is the Firebird page → DATEDIFF()

Now this function returns the number of years or months or days difference between two dates. But this cannot be used as is for determining ones age. To get a close, but not 100% accurate, age you can get the difference in days and divide that by 365.25. There is a bug in Base Firebird field calculations that decimals are incorrect but CAST will fix.

Below is a sample SQL statement which retrieves years, months, days, and the calculated method mentioned:

Select "name",
       DATEDIFF( year, "date1", CURRENT_DATE ) As years,
       DATEDIFF( month, "date1", CURRENT_DATE ) As months,
       DATEDIFF( day, "date1", CURRENT_DATE ) As days,
       CAST(DATEDIFF( day, "date1", CURRENT_DATE ) / 365.25 AS NUMERIC(6,3))
            As years_old from T1

This is to be entered using Create Query in SQL View.. and you need to turn on Run SQL command directly using toolbar icon or from the Edit menu item.

[marcosAoo]

thank you worked

[mariosv]

Please, if the answer solves the question click .

[jbalazsi]

I was looking to calculate and report the age in years and months from a birthday date in LO base having the firebird engine. Below you have the codes. So I have the birthday date in a table called “Contacts” and the field containing the birthday date called “DataNasterii”. I had to create two queries. The first calculate the age in year and months, but cannot be used to export the data in a report. The second query could be used to create a report.

The first query I called: “Query_Age” and edited in sql view. Paste the following code in it and run the sql command directly! I have also Name and Surname included in the query from the table.

SELECT "Name", "SurName", "DataNasterii", 
DATEDIFF(year, "DataNasterii", CURRENT_DATE)-
CASE WHEN extract(month FROM CURRENT_DATE ) < extract(month FROM "DataNasterii") OR extract(month FROM CURRENT_DATE ) = extract(month FROM "DataNasterii") AND 
extract(day FROM CURRENT_DATE ) < extract(day FROM "DataNasterii") THEN 1 ELSE 0 END AS "years", 
extract(month FROM CURRENT_DATE)-extract(month FROM "DataNasterii") +
CASE WHEN extract(month FROM CURRENT_DATE) <= extract(month FROM "DataNasterii") AND extract(day FROM CURRENT_DATE) < extract(day FROM "DataNasterii")
THEN 11 ELSE
CASE 
WHEN extract(month FROM CURRENT_DATE) < extract(month FROM "DataNasterii") AND extract(day FROM CURRENT_DATE) >= extract(day FROM "DataNasterii")
THEN 12 ELSE
CASE WHEN extract(month FROM CURRENT_DATE) > extract(month FROM "DataNasterii") AND extract(day FROM CURRENT_DATE) < extract(day FROM "DataNasterii")
THEN -1 ELSE 0
END
END
END AS "months"  FROM "Contacts"

The second query is from the first query and I called it: Query_Age_Report.

SELECT * FROM "Query_Age"

Based on the second query you can create a report from LO. Hope it is helpful for somebody.