Ask Your Question
0

Using DATEDIFF for a Base query column

asked 2019-12-25 20:31:54 +0100

updated 2019-12-26 17:06:54 +0100

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...
image description

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-12-25 20:52:12 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more

Comments

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.

Ratslinger gravatar imageRatslinger ( 2019-12-25 20:56:00 +0100 )edit

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

markroth gravatar imagemarkroth ( 2019-12-26 17:04:37 +0100 )edit

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"
Ratslinger gravatar imageRatslinger ( 2019-12-26 17:53:12 +0100 )edit
1

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

markroth gravatar imagemarkroth ( 2019-12-27 16:09:54 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-25 20:31:54 +0100

Seen: 415 times

Last updated: Dec 26 '19