Calculating age in years 1

[notadba]

I know this should be easy(!) but it is a little late on a Friday...

I need to identify age in whole years as at run date for a query I am writing. Can anyone provide the sql syntax. (been working on sql server and DB2 too much lately.)

Have emplid, birthdate, need to derive age in years to build an age profile.

I have another 'rougher' version that rolls up 5 year age bands using case, but now need the actual number as a value returned in a query.

Thanks in advance

 

[HarleyQuinn]

How about something like:

SELECT emplID, floor((trunc(sysdate)-birthdate)/365.25) as EmplAge
FROM <yourtable>

Is that something like what you are after?

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[feherke]

Hi

Or with the [tt]months_between[/tt] function :

select emplID,trunc(months_between(sysdate,birthdate)/12) as EmplAge from <yourtable>;

Feherke.

http://rootshell.be/~feherke/

 

[notadba]

Thanks for the feedback - will test it next chance. I was trying something along those lines of Feherke's example - but was getting errors. I think it is related to the format of the birthdate field (which was something like ddmmyyyy).

I am also trying to force this through a query tool which adds its own formatting when creating expressions.

Thanks for the help, will let you know.

 

[feherke]

Hi

I think it is related to the format of the birthdate field (which was something like ddmmyyyy).

Format of birthday field ? Then what is the type of that field ? I assumed is [tt]date[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[notadba]

Hi Feherke,

Finally back and working on the issue again. It appears that the problem is the way in which the application query tool handles dates is inconsistent with the database version. It is a date field...but...

I almost have it except that I am getting the error:
ORA-01861: literal does not match format string

The query produces the following sql when just testing the date formats:

SELECT A.EMPLID, A.NAME, A.SEX, TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'), sysdate
FROM EMPL_DATA A

ID NAME GENDER Birthdate sysdate

X X F 12/12/1976 01/JUN/06
Y Y M 02/05/1972 01/JUN/06
Z Z F 07/12/1954 01/JUN/06

The format of the date field in the 'query return' is DD/MM/YYYY, yet the sql produced is to_char(birthdate,'YYYY-MM-DD')

I have tried adding the following expression:

trunc(months_between(to_char(sysdate,'YYYY-MM-DD'), TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'))/12)
**the TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD' is added by the query tool. I get the ORA-01861: literal does not match format string, and patience is wearing thin.

Any ideas?

 

[notadba]

Finally solved (with lots of help)

The query tool was converting the date field to the incorrect format, so needed to be forced back into being a date.

Final syntax...

SELECT A.EMPLID, A.NAME, A.SEX, TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'), TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE( TO_CHAR(A.BIRTHDATE,'YYYY-MM-DD'),'YYYY-MM-DD'))/12)
FROM EMPL_DATA A

And these query tools are supplied to make life easier for the end-user....