How to display the difference of two date fields on a Base form?

[tommasz]

My HSQLDB has a pair of date fields that I’d like to use to calculate a duration to display on the input form. The form is based on a query that contains a bunch of fields including one that has a numeric calculation based on two fields. But it appears I can’t do any calculations on date values in the query so I thought I’d do it on the form. The Days() function is what I want to use but so far I haven’t been able to do that. If I put that in a Numerical Field’s Data Value it never displays anything. I’ve been unable to find anything useful on this so far.

LO v6.1.5.2
Mac OS X 10.14.4

[anon73440385]

DATEDIFF doesn’t work for you in SQL?

[tommasz]

It does, thanks. Everything I had googled made it look like it was specific to MS SQLServer.

[lonk]

Sorry for cutting in, what is the result of DATEDIFF for May 1, 2019 and May 2, 2019 ?

[anon73440385]

@lonk My SQL query (SELECT "Date1", "Date2", DATEDIFF( 'dd', "Date1", "Date2" ) FROM "Datum" AS "Days") calculates it to “1” - cannot show a screenshot since comments don’t support upload of images; so you need to trust me

[lonk]

@anon73440385
Thank you so much. I’m busy learning Calc now. And in the near future, I will move forward to Base. It will be useful for me.

[anon73440385]

Just for the record, that there is a correct answer:

Use DATEDIFF as described in SQL FAQ

To show other users your question has been answered, click the check mark () next to the answer.