Home » SQL & PL/SQL » SQL & PL/SQL » No of days approaching birthday
No of days approaching birthday [message #184917] Fri, 28 July 2006 12:03 Go to next message
Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore
Senior Member

Hi,

I am trying find the "Age Approaching" and the no of days ahead for the employee's birthday.

I was able to find the age of the employee's and couldn't able to find the no of remaining for an employee.

for ex, if an employee is born 30th July - then the "Days ahead for the birthday" should be 2 days for an employee who is on his 18th or 21st or 25th birthday.

select papf.last_name,papf.first_name,papf.employee_number
,papf.DATE_OF_BIRTH , round(months_between(sysdate,papf.DATE_OF_BIRTH)/12) "Age Approaching"
from per_all_people_f papf
,per_all_assignments_f paaf
,PAY_ELEMENT_ENTRIES_F peef
Where papf.PERSON_ID=paaf.PERSON_ID
AND peef.ASSIGNMENT_ID=paaf.ASSIGNMENT_ID

Could any one help me on this ,please?
Re: No of days approaching birthday [message #184953 is a reply to message #184917] Fri, 28 July 2006 19:30 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
SELECT trunc(to_date('31-jul-2006', 'dd-mon-yyyy')) - trunc(SYSDATE)
FROM dual
Re: No of days approaching birthday [message #184980 is a reply to message #184917] Sat, 29 July 2006 08:32 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member



SQL> SELECT age_approaching ,
  2         ADD_MONTHS(birth_day,age_approaching*12) -TRUNC(SYSDATE) No_of_day
  3  FROM (SELECT TO_DATE('30-july-1980','dd-Mon-yyyy') birth_day,
  4        CEIL(
  5       MONTHS_BETWEEN (SYSDATE,TO_DATE('30-july-1980','dd-Mon-yyyy'))
  6            /12)
  7        age_approaching
  8        FROM dual)
  9  /

AGE_APPROACHING  NO_OF_DAY
--------------- ----------
             26          1

SQL> SELECT age_approaching ,
  2         ADD_MONTHS(birth_day,age_approaching*12) -TRUNC(SYSDATE) No_of_day
  3  FROM (SELECT TO_DATE('30-jun-1980','dd-Mon-yyyy') birth_day,
  4        CEIL(
  5       MONTHS_BETWEEN (SYSDATE,TO_DATE('30-jun-1980','dd-Mon-yyyy'))
  6            /12)
  7        age_approaching
  8        FROM dual)
  9  /

AGE_APPROACHING  NO_OF_DAY
--------------- ----------
             27        336

SQL>


Thumbs Up
Rajuvan.
Previous Topic: email no content
Next Topic: How to Modify this Query to make it loop through Certain defined intervals
Goto Forum:
  


Current Time: Thu Dec 08 10:21:58 CST 2016

Total time taken to generate the page: 0.11297 seconds