Using DATEDIFF for a Base query column

[markroth]

LO 6.2.8.2 on Win7

I have a query in which I use a function in a column to show results under the alias Age.

This works using the HSQLDB engine:

DATEDIFF(‘yy’, “Name and Dates”.“Birth”, CURRENT_DATE)

I’ve invested much research and experimentation trying to find a Firebird version that works. I’m finally asking for help.

This (and any variations that use other placements for the quotation marks) does not work:

DATEDIFF(year, “Name and Dates”.Birth, CURRENT_DATE)

I also tried a space-free table name like this:

DATEDIFF(year, NameDates.Birth, CURRENT_DATE)

and got this error message – Field name…does not exist.

I even tried this:

DATEDIFF(year, “NameDates.Birth”, CURRENT_DATE)

and got this error message – Column…is unknown.

Eventually I tried this:

DATEDIFF(year, Birth, Wedding)

and got this error message – Field name…does not exist.

Can a function not be used for a Base query column in Firebird like it can be in HSQLDB?

If so, how?!

Thank you,
Mark

Edited on 12-26-19 to add this screen capture…

[Ratslinger]

Hello,

You were close but don’t understand why the change in field definition. With mixed case as you have you need to surround table/field names with quotes:

"NameDates"."Birth"

or

"Birth"

See this post for DATEDIFF → FireBird, Libreoffice Base DATEDIFF. The answer also contains other links to further documentation.

[Ratslinger]

Note that “NameDates” is copied from later example. First example is “Name and Dates”. This is a table name and you should know which is correct.

[markroth]

Thank you, Ratslinger.

Yeah, I’d already tried surrounding names with quotes before my OP. That’s part of what I meant with my “any variations” parenthetical:

This (and any variations that use other placements for the quotation marks) does not work:

DATEDIFF(year, “Name and Dates”.Birth, CURRENT_DATE)

You wonder about the change in field definition. In desperation, I’d created a new table with no spaces in the name to see if that would make a difference. (It didn’t.)

Anyway, a few minutes ago I tried this version of the function:

DATEDIFF(year, “NameDates”.“Birth”, CURRENT_DATE)

(I’ll see if I can add a screen capture to the OP…)

Each generated this error: Column…is unknown. (Where the ellipses represents the entire function.)

Thanks,
Mark

[Ratslinger]

Typically I do not use query ‘Design’ mode but rather SQL directly. Also don’t know if you turned on Run SQL command directly as stated in the provided link.

Doing this in SQL mode works without a problem.

Select DATEDIFF(year, "NameDates"."Birth", CURRENT_DATE) From "NameDates"

[markroth]

Running the function “in SQL mode works without a problem” for me as well.

However, I want to avoid getting into SQL mode for the entry-level course I’m working on. (I don’t mind using SQL mode for my personal stuff.) But since Firebird doesn’t allow this function to be used in the Query Design window, I guess I’ll have to reevaluate my original decision to avoid SQL mode…or else just drop the notion of doing this kind of calculation in a query.

Thank you again!
Mark