Page 1 of 1

### [Solved] Calculated Fields in Base

Posted: Sun Nov 21, 2010 1:09 pm
Hi

In my Members table I have a DOB category and an AGE category and I would like the AGE to be a calculated field based upon the DOB and the current date. In a spreadsheet the formula is =YEAR(NOW()-"the DOB cell")-1900 but I do not think there is an equivalent formula in SQL.

The DATEDIFF function using the DOB and the current date appears to be the way forward but bearing mind I need a number not a date i am unsure how to achieve this in SQL.

Any help or guidance will be appreciated.

grabro

### Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 2:42 pm

### Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 6:12 pm
You said / asked:

grabro wrote:I would like the AGE to be a calculated field based upon the DOB and the current date.

Assuming the name of the field in your database is "Date of Birth" ( if not, change the code below to reflect the Field name in your Database Table ) . . . the following calculation will return an Age in years ( whole number ).

Code: Select all   Expand viewCollapse view
`DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0)`

Important Note: The formula you showed above ( =YEAR(NOW()-"the DOB cell")-1900 ) for a Spreadsheet . . . will provide invalid values. For example . . . assume today in YYYY-MM-DD format is 2010-11-21 . . . if "the DOB cell" is 2008-11-20 or 2008-11-21 . . . incorrect values are returned .

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

### Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 7:22 pm
Sliderule

Thank you for your help. I am sure this will work if only I can now work out the SQL syntax for it. Those I have trie keep giving me an error.

Incidentally in MYSQL the following query works OK

SELECT Members SET AGE=((DATEDIFF(CURDATE(),DOB))/365 where ID<609

I'll get there eventually

grabro

### Re: Calculated Fields in Base

Posted: Sun Nov 21, 2010 7:38 pm
grabro:

You comment above does not make sense to be. Please be specific . . . ¿ ¿ ¿ what is your database engine . . . is it HSQL ( the default used by OpenOffice Base ) . . . or . . . MySQL ( which you mentioned in your above post ) . . . or some other Database Engine ? ? ?

Please tell me EXACTLY what the status bar at the bottom displays, after you open your OpenOffice *.odb file?

Sliderule

### Re: Calculated Fields in Base

Posted: Mon Nov 22, 2010 12:32 am
Sliderule

Sorry i was unclear. I am using HSQL. The database was developed by Openoffice Base. My reference to Mysql was mainly because I was under the impression that the SQL syntax was standard throughout all databases.

Originally I was using Mysql but I could not get it to work through oOo Base; it does not recognize the JBDC files. Accordingly I set up the oOoBase system as i am hoping that eventually it will be set up so that people used to Microsoft Access can use it without any problems.

So, Ignore my reference to Mysql.

grabro

### Re: Calculated Fields in Base

Posted: Mon Nov 22, 2010 1:17 am
Since you are using HSQL ( my original assumption ) as your database engine . . . you may use EITHER of code samples below to calculate AGE . . . but change "Date of Birth" ( three times ) to the actual field in your database table:

Code: Select all   Expand viewCollapse view
`DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0)     orYEAR(CURRENT_DATE) - YEAR("Date of Birth") - CASEWHEN(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth"),0,1)`

Please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:13 pm
Hello Sliderule,

Your sample code looks like it would solve my challenge, not quite sure. I want the same as the originator of the thread, a number/age. The difference is I want it to display in a form. Where would I put it...in the Control or on the form with the Control? As usual your help is appreciated.

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:27 pm
Code: Select all   Expand viewCollapse view
`SELECT *, DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"FROM "YourTable"`

Returns everything from "YourTable" plus a calculated field "Age". Replace your form source with this query.

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 3:56 pm
Wow thanks Villeroy for the quick reply...trying it out. Will let you know.

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 4:07 pm
Villeroy/Sliderule,

I got the following syntax error message:

Unexpected "" expecting From or INTO

When opening the Form.

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 5:17 pm
iqullc:

You said:

"iqullc wrote:I got the following syntax error message:

Unexpected "" expecting From or INTO

Therefore, you made a mistake. Since, you have elected NOT to inform us exactly what your Query looks like, the only answer, based on the information you have provided is, you made a mistake.

Sliderule

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:06 pm
Oops...I thought you could see the thread.

SELECT *,
DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"
FROM "YourTable"

I replaced "Date of Birth" with "Bday" and "your table" with "Patients"

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:17 pm
I asked you to show us EXACTLY the SQL you wrote . . . and . . . you replied above:

Code: Select all   Expand viewCollapse view
`SELECT *, DATEDIFF('YY', "Date of Birth", CURRENT_DATE) - CASEWHEN((MONTH("Date of Birth")*100 + DAYOFMONTH("Date of Birth")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"FROM "YourTable"`

Change the above to:

Code: Select all   Expand viewCollapse view
`SELECT "Patiens".*, DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) AS "Age"FROM "Patients"`

Explanation: Since, you want to include all of the columns ( fields ) from the table "Patients" in the RESULT SET, you should include that table name BEFORE the * in the Query. I replaced the table name to "Patients, and, the column containing the date to, "Bday" .

Sliderule

### Re: Calculated Fields in Base

Posted: Mon Dec 09, 2013 7:22 pm
Try the joker with table name:
SELECT "Patients".*, ...etc...

### Re: Calculated Fields in Base

Posted: Thu Dec 12, 2013 6:34 pm
Fabulous, thanks Sliderule and Villeroy. Everything is working.

On the calculation script and error message...duh on my part

See you around the Forum.

Stephanie

### Re: [Solved] Calculated Fields in Base

Posted: Sat Mar 11, 2017 3:41 am
Thanks Sliderule! Just downloaded Base today and needed the Age calculation. Worked perfect first time. Just need something that will return 'blank' when there is no birthday. Steep learning curve! ;>)

### Re: [Solved] Calculated Fields in Base

Posted: Sat Mar 11, 2017 7:29 am
Quadium wrote:Thanks Sliderule! Just downloaded Base today and needed the Age calculation. Worked perfect first time. Just need something that will return 'blank' when there is no birthday. Steep learning curve! ;>)

Welcome to the OpenOffice / LibreOffice Base forum.

Use the following code:

Code: Select all   Expand viewCollapse view
`SELECT    "Patients".*,    COALESCE(DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0),NULL) AS "Age"FROM "Patients"`

Explanation: Using the COALESCE function, which says, if the first argument is NOT NULL, use it, otherwise, return the second argument . . . in this case . . . NULL .

Sliderule

### Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 6:14 pm
Sliderule - When I use DATEDIFF( 'YY', "G_DateOfBirth", CURRENT_DATE ) - CASEWHEN( ( MONTH( "G_DateOfBirth" ) * 100 + DAYOFMONTH( "G_DateOfBirth" ) ) > ( MONTH( CURRENT_DATE ) * 100 + DAYOFMONTH( CURRENT_DATE ) ), 1, 0 ) in the Field of a Query, with Age as the Alias, it returns the Age properly, but Age is 0 when DateOfBirth is blank. When I try to modify it to COALESCE(same as previous,NULL) I get an error: The column 'COALESCE(DATEDIFF( 'YY', "G_DateOfBirth", CURRENT_DATE ) - CASEWHEN( ( MONTH( "G_DateOfBirth" ) * 100 + DAYOFMONTH( "G_DateOfBirth" ) ) > ( MONTH( CURRENT_DATE ) * 100 + DAYOFMONTH( CURRENT_DATE ) ), 1, 0 ),NULL)' is unknown. What am I doing wrong?

### Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 6:25 pm
Please note, in my opinion, your database should NEVER have a NULL ( undefined ) value, whether with a TEXT column, a DATE column, a TIME column, a TIMESTAMP column, a BOOLEAN column, nor, a NUMERIC column.

Try the following code:

Code: Select all   Expand viewCollapse view
`SELECT    "Patients".*,    CASE WHEN "Bday" IS NULL THEN NULL ELSE DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0) END CASE AS "Age"FROM "Patients"`

Or, perhaps the following code . . . that MUST be run withOUT the Base Parser:

Code: Select all   Expand viewCollapse view
`SELECT    "Patients".*,    CASEWHEN ("Bday" IS NULL, NULL, DATEDIFF('YY', "Bday", CURRENT_DATE) - CASEWHEN((MONTH("Bday")*100 + DAYOFMONTH("Bday")) > (MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE)),1,0))  AS "Age"FROM "Patients"`

Explanation: Changed from the previously posted COALASE function to a CASE WHEN . . . THEN . . . ELSE . . . END CASE statement.

I hope this helps, please be sure to let me / us know.

Sliderule

### Re: [Solved] Calculated Fields in Base

Posted: Sun Mar 12, 2017 7:21 pm
Sliderule - Same error message for both new suggested formulas. If you can access my e-mail address, send me a quick note, and I'll forward the database. It's a small one I created just to start learning what I need. Thx.