0

So this question seems to be asked in various forms in programming classes. I've seen this asked with triggers, functions, and such. I've calculated the leap year part of the program, and there are a couple of different ways to calculate age from your birthday, whether dividing by 365.25 or intervals months between and probably a couple of other ways to go about this the best way.

I'm just having some problems with my Age calculation and am getting information overload on doing this in other programs and specifically programming this in PL/SQL for Oracle Dev.

This one shows how it's done in SQL SERVER: calculating age from sysdate and birthdate using SQL Server

This one uses triggers and it is similar but not quite what I'm looking for. Calculating age from birthday with oracle plsql trigger and insert the age in table

Write a PL/SQL block to accept your birthdate. Calculates and prints your age in years including one decimal point. Also checks whether your birth year is a leap year or not. Print a message like “My birth year is a leap year.” or “My birth year is not a leap year.” Hint: To determine the leap year, the year should be exactly divisible by 4 but not divisible by 100, or it should be divisible by 400.

SET SERVEROUTPUT ON
DECLARE
   v_birthday_year        NUMBER(4)    := &v_birthday_year;
   v_your_age             NUMBER(4, 1);
   v_leap_remainder1      NUMBER(5, 2);
   v_leap_remainder2      NUMBER(5, 2);
   v_leap_remainder3      NUMBER(5, 2);

 BEGIN
   v_leap_remainder1 := MOD(v_birthday_year, 4);
   v_leap_remainder2 := MOD(v_birthday_year, 100);
   v_leap_remainder3 := MOD(v_birthday_year, 400);

 IF ((v_leap_remainder1 = 0 AND v_leap_remainder2 <> 0 ) 
     OR v_leap_remainder3 = 0) 
 THEN
   DBMS_OUTPUT.PUT_LINE(v_birthday_year || ' is a leap year');

 ELSE
   DBMS_OUTPUT.PUT_LINE (v_birthday_year || ' is not a leap
   year');

  END IF;


  TO_CHAR(SYSDATE, 'DD-MM-YYYY')
  v_your_age := (MONTHS_BETWEEN(TRUNC(SYSDATE), v_birthday_year)/12);
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);

 END;
 /

Actually I figured it out, it was a to_date conversion for the dob, separate variables for birthyear, and dob, and age. Age calculated to 1 decimal. Trunc(x, 1) Works great now.

Community
  • 1
  • 1
MeachamRob
  • 363
  • 3
  • 8
  • 19
  • 2
    So what is your actual question? – Vicky Feb 06 '13 at 10:05
  • just comment the `TO_CHAR(SYSDATE, 'DD-MM-YYYY')` line. – Florin Ghita Feb 06 '13 at 10:10
  • 1
    What is your question? – Linga Feb 06 '13 at 10:11
  • Thank you for your help, I was having problems specifically with the date conversion and the calculation. I put the comment in my code where I was having the problem, and just wanted some guidance on how to best go about this problem. Specifically, there is an information overload as professors ask this question in every programming class. But this is probably the best hint if I can combine it into my coding. I am just going over my train of thought and publish this to help others. http://stackoverflow.com/questions/3015431/oracle-age-calculation-from-date-of-birth-and-today – MeachamRob Feb 06 '13 at 22:38
  • Yeah I don't know why I put the TO_CHAR(SYSDATE, 'DD-MM-YYYY') in there, I was just writing in code that might be useful, I was having issues with the variable declared as a NUMBER, but examples i have seen that calculate the age use DATE variable, so that was throwing me off. – MeachamRob Feb 06 '13 at 22:50

1 Answers1

1

You can also try to check it with conversions to Julian dates. When you convert a timestamp to a Julian date you get the number of days since some date before -4000 BC. If you calculate the difference between two dates you know the number of days between those dates. So do it for January 1st and December 31st of you birth year and you'll know if it was leap year or not.

For calculating age check the function months_between (check the last answer for this question).

Community
  • 1
  • 1
Fábio Oliveira
  • 2,346
  • 21
  • 30