Saturday, January 11, 2014

Write a SQL program to accept the users birthdate and calculates the Age.

create table Persons(id int, name varchar2(40), dob date);

insert into Persons values(1,'ranga','01-JUN-88');
insert into Persons values(2,'eswar','01-JUL-88');
insert into Persons values(3,'vinod','01-APR-87');

1. SELECT p.*, trunc( (sysdate - dob) / 365.25) as age FROM Persons p;
2. SELECT p.*, floor(months_between(sysdate,dob)/12) age FROM Persons p;
3. SELECT  p.*, trunc((sysdate - p.dob)/365.25) as age FROM Persons p;


Displaying the Year Month Day

SELECT trunc(months_between(sysdate,dob)/12) as YEAR,
       trunc(mod(months_between(sysdate,dob),12)) as MONTH,
       trunc(sysdate - add_months(dob,trunc(months_between(sysdate,dob)/12) *12 + trunc(MOD(months_between(sysdate,dob),12)))) DAY
FROM (SELECT p.dob dob FROM Persons p);