| View previous topic :: View next topic |
| Author |
Message |
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
 Posted: Tue Nov 18, 2008 1:20 am Post subject: [Solved] Newbie : Trying to calculate Age |
 |
|
Hi there I'm trying to calculate Age
From
http://wiki.services.openoffice.org/wiki/Built-in_functions_and_Stored_Procedures#Date_and_Time_Functions
I found the syntax indicated as
DATEDIFF(string, datetime1, datetime2)
Table name Employees
The field I input in are
a) LastName
b) BirthDate
So I tried
DATEDIFF( 'yy', `BirthDate`, NOW( ) )
But the output for age is 0 for all the so call employees.
Under SQL view the statement as follow
SELECT `LastName` AS `Name`, `BirthDate` AS `Birth Date`, DATEDIFF( 'yy', `BirthDate`, NOW( ) ) AS `Age` FROM `Employees`
Where did I go wrong?
Help please......
 [/img]
Last edited by Forever Jahat on Thu Nov 20, 2008 8:18 pm; edited 1 time in total |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Nov 18, 2008 2:36 am Post subject: Re: Newbie : Trying to calculate Age |
|
|
Hello
You have to use double quotes for variables and single quotes for literals.
So I think it must be.
| Code: |
DATEDIFF( 'yy', "BirthDate", NOW( ) )
SELECT "LastName" AS "Name", "BirthDate" AS "Birth Date", DATEDIFF( 'yy', "BirthDate", NOW( ) ) AS "Age" FROM "Employees"
|
Romke |
|
| Back to top |
|
|
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
| Posted: Tue Nov 18, 2008 6:06 pm Post subject: |
|
|
Thank you for the advice.
I tried exactly as instructed.
However still the same result.
Age = 0
SELECT "LastName" AS "Name", "BirthDate" AS "Birth Date", DATEDIFF( 'yy', "BirthDate", NOW( ) ) AS "Age" FROM "Employees"
When i checked again, it would automatically convert back to
SELECT `LastName` AS `Name`, `BirthDate` AS `Birth Date`, DATEDIFF( 'yy', `BirthDate`, NOW( ) ) AS `Age` FROM `Employees`
Seems like it defaulted itself to the single quote.
Now what could be the problem?
|
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
|
| Posted: Tue Nov 18, 2008 6:16 pm Post subject: |
|
|
Forever Jahat:
Can you please tell me ( us ) . . . what database engine are you using?
Let me attempt to explain . . . OpenOffice Base . . . has a 'default' database . . . that is HSQL. You can confirm this, after opening your OpenOffice base database file . . . and . . . on the status bar . . . AT THE BOTTOM . . . what does it say?
Is it the 'default' HSQL embedded database . . . OR . . . are you 'connected' to some other database, for example, MySQL, MS Access, SQLite, PostGreSQL, dBase, Oracle, other?
The reason, the 'syntax' of the SQL functions . . . like . . . DATEDIFF depends on the SQL engine that Base will be passing the function to. Also, just to be certain . . . what kind ( field type ) is "BirthDate" ( or is it `BirthDate` ) ? That is, is it a DATE, or, a TIMEVALUE ( DATE followed by TIME )? I ask, because . . . NOW() will return the DATE and TIME . . . whereas, CURRENT_DATE will only return a DATE.
I hope this helps, please be sure to let me / us know.
Sliderule |
|
| Back to top |
|
|
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
| Posted: Wed Nov 19, 2008 11:29 pm Post subject: |
|
|
Sorry did not know that.
I was using the database from NorthWind.
A sample database from MS Access 2007
Only when you mentioned it then I noticed.
I tried creating a new raw data using HSQL database engine and it works.
Now it make me wonder.
If for some reason I am force to use a database from MS Access.
How do I settle the problem?
 |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2473
Location: 3rd Rock From The Sun
|
| Posted: Thu Nov 20, 2008 9:25 am Post subject: |
|
|
Forever Jahat:
You asked: | Forever Jahat wrote: | If for some reason I am force to use a database from MS Access.
How do I settle the problem? |
If you are using an Access database within OpenOffice Base . . . you are telling base to READ the data residing in the Access file, and, using the connection you have established, such as ODBC etc, to get the data. The point, the data is NOT duplicated, it only resides once on your computer, and, you have to 'follow' the rules of the ODBC driver.
In your case, the graphic below shows a way to accomplish this task using a 'sample' Access database file. BUT, as an FYI ( For Your Information ), whether using HSQL or Access, if you really want to determine the 'age' the result will depend ALSO on the day of the year. For example, if we meant the age of a person, depends whether the birth date ( anniversary of date of birth ) has yet passed. So, an additional 'condition' ( CASEWHEN for HSQL, IIF for Access ) might be added depending on the DAYOFYEAR of the Date file vs NOW() .
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post title (edit button) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
Forever Jahat
General User
Joined: 02 Nov 2008
Posts: 9
|
| Posted: Thu Nov 20, 2008 8:17 pm Post subject: |
|
|
hi sliderule
thanks for the explanation
i now understand on which rules to follow when connecting to other database
now the age calculation appear nicely.
since you did mentioned about whether the date have passed or not I actually have some question on Case When
i did some search and found
Calculating Age Control on Form from Birthdate Control
http://www.oooforum.org/forum/viewtopic.phtml?t=26140&highlight=age
i guess it's better i make the enquiry on that forum.
thanks guys for the help  |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
