OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Newbie : Trying to calculate Age

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Tue Nov 18, 2008 1:20 am    Post subject: [Solved] Newbie : Trying to calculate Age Reply with quote

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

Confused [/img]


Last edited by Forever Jahat on Thu Nov 20, 2008 8:18 pm; edited 1 time in total
Back to top
View user's profile Send private message
RPG
Super User
Super User


Joined: 24 Apr 2008
Posts: 2697
Location: Apeldoorn, Netherland

PostPosted: Tue Nov 18, 2008 2:36 am    Post subject: Re: Newbie : Trying to calculate Age Reply with quote

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
View user's profile Send private message
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Tue Nov 18, 2008 6:06 pm    Post subject: Reply with quote

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?
Confused
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Nov 18, 2008 6:16 pm    Post subject: Reply with quote

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
View user's profile Send private message
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Wed Nov 19, 2008 11:29 pm    Post subject: Reply with quote

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?

Question
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Thu Nov 20, 2008 9:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Forever Jahat
General User
General User


Joined: 02 Nov 2008
Posts: 9

PostPosted: Thu Nov 20, 2008 8:17 pm    Post subject: Reply with quote

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 Razz
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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