OraFAQ Forum: SQL & PL/SQL » Calculate AGE of Employee when Birth Date column is non date type.

Home » SQL & PL/SQL » SQL & PL/SQL » Calculate AGE of Employee when Birth Date column is non date type.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calculate AGE of Employee when Birth Date column is non date type. [message #35408]

Wed, 09 February 2005 14:53

JSchuh
Messages: 2
Registered: February 2005

Junior Member

I am trying to calculate the AGE of employees in a table which does not use a DATE datatype for the Birthdate.

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35409 is a reply to message #35408]

Wed, 09 February 2005 15:23

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Shall we guess that it is a VARCHAR2 datatype then? Shall we try to guess in what format it is stored?

You make a statement (in the negative), ask no question, and provide no sample data - not a lot to go on.

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35410 is a reply to message #35409]

Wed, 09 February 2005 15:39

JSchuh
Messages: 2
Registered: February 2005

Junior Member

Hi Todd,
I am working with a simple Oracle database TABLE with the following specs;

HR_EMPL_CODE VARCHAR2(10)
HR_BRTH_DATE VARCHAR2 (8)
HR_EMPL_NAME VARCHAR2 (40)

The format of the HR_BRTH_DATE is ddMMMyy - 10Feb05.

I am trying to plug a formula into a crystal report which will display the EMPLOYEES age.

Please excuse my ambiguity.

Best Regards,

Jas.

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35411 is a reply to message #35410]

Wed, 09 February 2005 18:57

anil sinare
Messages: 61
Registered: November 2004
Location: pune

Member

Hi,

DROP TABLE Test

CREATE TABLE Test
(
HR_EMPL_CODE VARCHAR2(10),
HR_BRTH_DATE VARCHAR2 (8),
HR_EMPL_NAME VARCHAR2 (40)
);

INSERT INTO Test ( HR_EMPL_CODE, HR_BRTH_DATE, HR_EMPL_NAME )
VALUES ('1', '12Dec78', 'anil');
INSERT INTO Test ( HR_EMPL_CODE, HR_BRTH_DATE, HR_EMPL_NAME )
VALUES ('2', '10May85', 'sachin');
INSERT INTO Test ( HR_EMPL_CODE, HR_BRTH_DATE, HR_EMPL_NAME )
VALUES ('3', '15Jul00', 'rahul');
INSERT INTO Test ( HR_EMPL_CODE, HR_BRTH_DATE, HR_EMPL_NAME )
VALUES ('4', '20Oct01', 'vikram');
COMMIT;

SELECT
HR_EMPL_CODE,
HR_EMPL_NAME,
hr_brth_date,
Bdate,
FLOOR(MONTHS_BETWEEN(SYSDATE,Bdate)/12)years,FLOOR(MONTHS_BETWEEN(SYSDATE,Bdate)-FLOOR(MONTHS_BETWEEN(SYSDATE,Bdate)/12)*12) months
FROM
(SELECT
HR_EMPL_CODE,
HR_EMPL_NAME,
hr_brth_date,
SYSDATE - TO_DATE(DECODE(FLOOR(TO_NUMBER(SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1))/6),0,SUBSTR(hr_brth_date,1,LENGTH(hr_brth_date)-2)||'20'||SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1),SUBSTR(hr_brth_date,1,LENGTH(hr_brth_date)-2)||'19'||SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1)),'ddMONyyyy')Days,
TO_DATE(DECODE(FLOOR(TO_NUMBER(SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1))/6),0,SUBSTR(hr_brth_date,1,LENGTH(hr_brth_date)-2)||'20'||SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1),SUBSTR(hr_brth_date,1,LENGTH(hr_brth_date)-2)||'19'||SUBSTR(hr_brth_date,LENGTH(hr_brth_date)-1)),'ddMONyyyy') Bdate
FROM Test);

output is -

HR_EMPL_CODE HR_EMPL_NAME HR_BRTH_DATE BDATE YEARS MONTHS
-----------------------------------------------------------------------------
1 anil 12Dec78 12/12/1978 26 1
2 sachin 10May85 5/10/1985 19 9
3 rahul 15Jul00 7/15/2000 4 6
4 vikram 20Oct01 10/20/2001 3 3

If any one have SHORT solution to this, please post that.

Hope this helps you.

Thanks and Regards
Anil Sinare

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35412 is a reply to message #35411]

Wed, 09 February 2005 19:40

pandian
Messages: 31
Registered: December 2004

Member

SELECT
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(TO_NUMBER(SUBSTR(DOB,1,2))||'-'||SUBSTR(DOB,3,3)||'-'||TO_NUMBER(SUBSTR(DOB,-2)),'DD-MON-RR'))/12) YEArs,
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(TO_NUMBER(SUBSTR(DOB,1,2))||'-'||SUBSTR(DOB,3,3)||'-'||TO_NUMBER(SUBSTR(DOB,-2)),'DD-MON-RR'))-
FLOOR(MONTHS_BETWEEN(SYSDATE, TO_DATE(TO_NUMBER(SUBSTR(DOB,1,2))||'-'||SUBSTR(DOB,3,3)||'-'||TO_NUMBER(SUBSTR(DOB,-2)),'DD-MON-RR'))/12)*12) MONTHSS,
TO_DATE(TO_NUMBER(SUBSTR(DOB,1,2))||'-'||SUBSTR(DOB,3,3)||'-'||TO_NUMBER(SUBSTR(DOB,-2)),'DD-MON-RR') dob FROM EEE
/

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35413 is a reply to message #35412]

Wed, 09 February 2005 19:49

anil sinare
Messages: 61
Registered: November 2004
Location: pune

Member

Hi,

Thanks for a great query. I was not aware of RR Date Format Element, first try to convert the date in YYYY format.

Thanks again
Anil Sinare

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35418 is a reply to message #35412]

Wed, 09 February 2005 21:34

Frank
Messages: 7901
Registered: March 2000

Senior Member

Use RR with great caution here, because of the sliding window:
a date of birth of 01Jan06 would be translated to 01 JAN 2006, meaning yet to be born instead of an age of 99!

You could use a decode (sign(age)...) to add a hundred years when the age becomes negative, but that still leaves you the people older than 100 years.
Bottomline: use 4 digits for dates (but I guess, that's too late for you now :)

hth

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35421 is a reply to message #35418]

Thu, 10 February 2005 00:16

pandian
Messages: 31
Registered: December 2004

Member

can u please give some sample queries to clear it
thank u for ur suggestion
pandian

Report message to a moderator

Re: Calculate AGE of Employee when Birth Date column is non date type. [message #35423 is a reply to message #35421]

Thu, 10 February 2005 02:01

Frank
Messages: 7901
Registered: March 2000

Senior Member

Thought it was pretty obvious:

ONTW>select to_char(to_date('01Jan06', 'ddMonRR'), 'dd-mm-yyyy') from dual;

TO_CHAR(TO
----------
01-01-2006

This should be 01-01-1906 if it's a date of birth.
And what about 01Jan05. Is this a 100 years old granny, or a baby of 1 month old?

hth

Report message to a moderator

Previous Topic:	reg.procedure.to return all the executed SQL stmts in a session
Next Topic:	sql query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Sep 08 11:46:39 CDT 2025