Oracle PL/SQL Tutorial/PL SQL Programming/TO DATE

TO_DATE("1/1/02","mm/dd/yy")

   <source lang="sql">

SQL> SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    d1    DATE;
 3    d2    DATE;
 4    d3    DATE;
 5    d4    DATE;
 6    n1    NUMBER;
 7    n2    NUMBER;
 8    n3    NUMBER;
 9   BEGIN
10    d1 := TO_DATE("1/1/02","mm/dd/yy");
11
12    DBMS_OUTPUT.PUT_LINE("d1 = " || TO_CHAR(d1,"dd-Mon-yyyy"));
13
14  END;
15  /

d1 = 01-Jan-2002 PL/SQL procedure successfully completed. SQL></source>

TO_DATE("1-1-1998","mm-dd-yyyy")

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    d1    DATE;
 3    d2    DATE;
 4    d3    DATE;
 5    d4    DATE;
 6    n1    NUMBER;
 7    n2    NUMBER;
 8    n3    NUMBER;
 9   BEGIN
10    d2 := TO_DATE("1-1-1998","mm-dd-yyyy");
11
12    DBMS_OUTPUT.PUT_LINE("d2 = " || TO_CHAR(d2,"dd-Mon-yyyy"));
13
14  END;
15  /

d2 = 01-Jan-1998 PL/SQL procedure successfully completed. SQL> SQL></source>

TO_DATE("Jan 1, 2000","mon dd, yyyy")

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    d1    DATE;
 3    d2    DATE;
 4    d3    DATE;
 5    d4    DATE;
 6    n1    NUMBER;
 7    n2    NUMBER;
 8    n3    NUMBER;
 9   BEGIN
10    d3 := TO_DATE("Jan 1, 2000","mon dd, yyyy");
11
12    DBMS_OUTPUT.PUT_LINE("d3 = " || TO_CHAR(d3,"Day, Month dd, yyyy"));
13
14  END;
15  /

d3 = Saturday , January 01, 2000 PL/SQL procedure successfully completed.</source>

Use TO_DATE function in PL/SQL

   <source lang="sql">

SQL> SQL> SET SERVEROUTPUT ON SQL> DECLARE

 2    test_date     DATE;
 3    day_of_week   VARCHAR2(3);
 4    years_ahead   INTEGER;
 5  BEGIN
 6    test_date := TO_DATE("1-Jan-1997","dd-mon-yyyy");
 7
 8    FOR years_ahead IN 1..10 LOOP
 9      day_of_week := TO_CHAR(test_date,"Dy");
10
11      IF day_of_week IN ("Sat","Sun") THEN
12        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| "     A long weekend!");
13      ELSE
14        DBMS_OUTPUT.PUT_LINE(TO_CHAR(test_date,"dd-Mon-yyyy")|| " Not a long weekend.");
15      END IF;
16      test_date := ADD_MONTHS(test_date,12);
17    END LOOP;
18  END;
19  /

01-Jan-1997 Not a long weekend. 01-Jan-1998 Not a long weekend. 01-Jan-1999 Not a long weekend. 01-Jan-2000 A long weekend! 01-Jan-2001 Not a long weekend. 01-Jan-2002 Not a long weekend. 01-Jan-2003 Not a long weekend. 01-Jan-2004 Not a long weekend. 01-Jan-2005 A long weekend! 01-Jan-2006 A long weekend! PL/SQL procedure successfully completed. SQL> SQL></source>

Using TO_DATE Within PL/SQL: TO_DATE("031092","MMDDYY")

   <source lang="sql">

SQL> DECLARE

 2       v_Convert_Date DATE;
 3  BEGIN
 4       v_Convert_Date := TO_DATE("031092","MMDDYY");
 5       DBMS_OUTPUT.PUT_LINE("The converted date is: " || v_Convert_Date);
 6  END;
 7  /

The converted date is: 10-MAR-92 PL/SQL procedure successfully completed.</source>