Oracle PL/SQL Tutorial/PL SQL Programming/TO CHAR

TO_CHAR(d3,"Day, Month dd, yyyy")

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.

TO_CHAR(n1,"999D99")

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    n1 := TO_NUMBER ("123.99","999D99");
 11
 12    DBMS_OUTPUT.PUT_LINE("n1 = " || TO_CHAR(n1,"999D99"));
 13
 14  END;
 15  /
n1 =  123.99
PL/SQL procedure successfully completed.

Use TO_CHAR function in DBMS_OUTPUT.PUT_LINE

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>

Use TO_CHAR function in PL/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>

Year 2000 problems? Note the effect of using rr instead of yy

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    d4 := TO_DATE("1/1/02","mm/dd/rr");
 11
 12    DBMS_OUTPUT.PUT_LINE("d4 = " || TO_CHAR(d4,"Dy, Mon dd, yyyy"));
 13
 14  END;
 15  /
d4 = Tue, Jan 01, 2002
PL/SQL procedure successfully completed.
SQL>