Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / PL SQL Data Types

 

Get value from different parts of a date using TO_NUMBER function

SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE   2    age   POSITIVE;   3   4    current_year  NATURAL;    --a year of 00 is valid.   5    current_month POSITIVE;   6    current_day   POSITIVE;   7   8    birth_year    NATURAL;    --a year of 00 is valid.   9    birth_month   POSITIVE;  10    birth_day     POSITIVE;  11  12    birth_date    DATE := TO_DATE('11-15-1961','mm-dd-yyyy');  13    current_date  DATE;  14  BEGIN  15    current_date := TO_DATE ('12-1-2000','mm-dd-yyyy');  16  17    current_year := TO_NUMBER(TO_CHAR(current_date,'yy'));  18    current_month := TO_NUMBER(TO_CHAR(current_date,'mm'));  19    current_day := TO_NUMBER(TO_CHAR(current_date,'dd'));  20  21    DBMS_OUTPUT.PUT_LINE(current_year);  22    DBMS_OUTPUT.PUT_LINE(current_month);  23    DBMS_OUTPUT.PUT_LINE(current_day);  24  END;  25  / 0 12 1 PL/SQL procedure successfully completed. SQL>