Mega Code Archive
Combine TO_NUMBER and TO_DATE to get different parts of a Date
<![CDATA[
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL> declare
2 age binary_integer;
3
4 birth_date date;
5 current_date date;
6
7 function age_as_of (birth_date in date, as_of_date in date) return positive is
8 as_of_year natural; --a year of 00 is valid.
9 as_of_month positive;
10 as_of_day positive;
11
12 birth_year natural; --a year of 00 is valid.
13 birth_month positive;
14 birth_day positive;
15
16 age positive;
17 begin
18 as_of_year := to_number(to_char(as_of_date,'yyyy'));
19 as_of_month := to_number(to_char(as_of_date,'mm'));
20 as_of_day := to_number(to_char(as_of_date,'dd'));
21
22 birth_year := to_number(to_char(birth_date,'yyyy'));
23 birth_month := to_number(to_char(birth_date,'mm'));
24 birth_day := to_number(to_char(birth_date,'dd'));
25
26 if as_of_month > birth_month then
27 age := as_of_year - birth_year;
28 elsif (as_of_month = birth_month) and (as_of_day >= birth_day) then
29 age := as_of_year - birth_year;
30 else
31 age := as_of_year - birth_year - 1;
32 end if;
33
34 return age;
35 end;
36 begin
37
38 current_date := to_date('11-13-1997','mm-dd-yyyy');
39 birth_date := to_date('11-15-1961','mm-dd-yyyy');
40
41
42 age := age_as_of (birth_date, current_date);
43 dbms_output.put_line(age);
44
45 end;
46 /
35
PL/SQL procedure successfully completed.
SQL>
]]>