Ask Your Question
0

[SOLVED] FireBird, Libreoffice Base DATEDIFF

asked 2019-06-16 15:27:57 +0200

marcosAoo gravatar image

updated 2019-06-17 18:27:06 +0200

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 fileC:\fakepath\datediff.odb

edit retag flag offensive close merge delete

Comments

@marcosAoo,

When cross posting, please note that this is done and where - also posted on -> https://forum.openoffice.org/en/forum...

This can save efforts!

Ratslinger gravatar imageRatslinger ( 2019-06-17 00:46:34 +0200 )edit
m.a.riosv gravatar imagem.a.riosv ( 2019-06-17 19:39:33 +0200 )edit

3 Answers

Sort by » oldest newest most voted
0

answered 2019-06-16 18:38:39 +0200

Ratslinger gravatar image

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.

edit flag offensive delete link more
0

answered 2020-03-08 22:36:49 +0200

updated 2020-03-08 22:39:12 +0200

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.

edit flag offensive delete link more
0

answered 2019-06-17 18:26:35 +0200

marcosAoo gravatar image

thank you worked

edit flag offensive delete link more

Comments

Please, if the answer solves the question click ✔.

m.a.riosv gravatar imagem.a.riosv ( 2019-06-17 19:37:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-06-16 15:27:57 +0200

Seen: 776 times

Last updated: Mar 08