Oracle PL/SQL/Conversion Functions/TO CHAR

CONVERSION functions: Print the current date/time as a character string and modifies the format

   
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     v_sysdate DATE := SYSDATE;
  3     v_date DATE;
  4     v_char VARCHAR2(20);
  5  BEGIN
  6
  7
  8     
  9     v_char := TO_CHAR(v_sysdate, "DD:MM:YYYY HH24:MI:SS");
 10     DBMS_OUTPUT.PUT_LINE("Display as CHARACTER DD:MM:YYYY HH24:MI:SS: "||v_char);
 11
 12
 13  END;
 14  /
Display as CHARACTER DD:MM:YYYY HH24:MI:SS: 26:10:2009 10:41:36
PL/SQL procedure successfully completed.
SQL>

convert a negative number into one with a trailing "minus sign,"

   
SQL>
SQL> SELECT
  2     TO_CHAR(-1234,"9999MI") result
  3  FROM dual;
RESUL
-----
1234-
SQL>

Pass variable into to_char function

   
SQL> DECLARE
  2     lv_format_txt  CONSTANT VARCHAR2(11) := "$99,999.99";
  3     lv_test_txt             VARCHAR2(11);
  4     lv_test_num             NUMBER := 54321;
  5  BEGIN
  6     lv_test_txt := TO_CHAR(lv_test_num, lv_format_txt);
  7     DBMS_OUTPUT.PUT_LINE("Test Value: " || lv_test_txt);
  8  END;
  9  /
Test Value:  $54,321.00
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>

The TO_CHAR() function converts the expression into character data.

   
Syntax: TO_CHAR(<expression> [,[FORMAT] [,NLS_PARAM]] )
SQL>
SQL> SELECT
  2     LENGTHB(NCHR(216))  national,
  3     LENGTHB(TO_CHAR(NCHR(216))) converted
  4  FROM dual;
  NATIONAL  CONVERTED
---------- ----------
         2          1

to_char(bdate,"fmMonth ddth, yyyy")

  
SQL>
SQL> create table emp
  2  ( empno      NUMBER(4)    constraint E_PK primary key
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , sal        NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2)    default 10
 11  ) ;
Table created.
SQL> insert into emp values(1,"Tom","N",   "TRAINER", 13,date "1965-12-17",  800 , NULL,  20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20",  1600, 300,   30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" ,  "Tester",6,date "1962-02-22",  1250, 500,   30);
1 row created.
SQL> insert into emp values(4,"Jane","JM",  "Designer", 9,date "1967-04-02",  2975, NULL,  20);
1 row created.
SQL> insert into emp values(5,"Mary","P",  "Tester",6,date "1956-09-28",  1250, 1400,  30);
1 row created.
SQL> insert into emp values(6,"Black","R",   "Designer", 9,date "1963-11-01",  2850, NULL,  30);
1 row created.
SQL> insert into emp values(7,"Chris","AB",  "Designer", 9,date "1965-06-09",  2450, NULL,  10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(9,"Peter","CC",   "Designer",NULL,date "1952-11-17",  5000, NULL,  10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28",  1500, 0,     30);
1 row created.
SQL> insert into emp values(11,"Ana","AA",  "TRAINER", 8,date "1966-12-30",  1100, NULL,  20);
1 row created.
SQL> insert into emp values(12,"Jane","R",   "Manager",   6,date "1969-12-03",  800 , NULL,  30);
1 row created.
SQL> insert into emp values(13,"Fake","MG",   "TRAINER", 4,date "1959-02-13",  3000, NULL,  20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager",   7,date "1962-01-23",  1300, NULL,  10);
1 row created.
SQL>
SQL> select ename
  2  ,      to_char(bdate,"fmMonth ddth, yyyy")
  3  from   emp;
Enter...
Tom      December 17th, 1965
Jack     Februari 20th, 1961
Wil      Februari 22nd, 1962
Jane     April 2nd, 1967
Mary     September 28th, 1956
Black    November 1st, 1963
Chris    Juni 9th, 1965
Smart    November 26th, 1959
Peter    November 17th, 1952
Take     September 28th, 1968
Ana      December 30th, 1966
Jane     December 3rd, 1969
Fake     Februari 13th, 1959
Mike     Januari 23rd, 1962
14 rows selected.
SQL> drop table emp;
Table dropped.

to_char(round(sqrt(sal),2),"9999.99")

  

SQL> CREATE TABLE EMP(
  2      EMPNO NUMBER(4) NOT NULL,
  3      ENAME VARCHAR2(10),
  4      JOB VARCHAR2(9),
  5      MGR NUMBER(4),
  6      HIREDATE DATE,
  7      SAL NUMBER(7, 2),
  8      COMM NUMBER(7, 2),
  9      DEPTNO NUMBER(2)
 10  );
Table created.
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select ename, sal, to_char(round(sqrt(sal),2),"9999.99")
  2         "Bonus" from emp;
ENAME             SAL Bonus
---------- ---------- --------
SMITH             800    28.28
ALLEN            1600    40.00
WARD             1250    35.36
JONES            2975    54.54
MARTIN           1250    35.36
BLAKE            2850    53.39
CLARK            2450    49.50
SCOTT            3000    54.77
KING             5000    70.71
TURNER           1500    38.73
ADAMS            1100    33.17
ENAME             SAL Bonus
---------- ---------- --------
JAMES             950    30.82
FORD             3000    54.77
MILLER           1300    36.06
14 rows selected.
SQL>
SQL> drop table emp;
Table dropped.

to_char(sysdate,"DAY dy Dy") as day, to_char(sysdate,"MONTH mon") as month

  
  
SQL>
SQL> select to_char(sysdate,"DAY dy Dy") as day
  2  ,      to_char(sysdate,"MONTH mon") as month
  3  from dual;
DAY             MONTH
--------------- -------------
MAANDAG   ma Ma OKTOBER   okt
SQL>
SQL>

to_char(sysdate, "Day", "nls_date_language=Dutch")

  

SQL>
SQL> select to_char(sysdate, "Day")
  2  ,      to_char(sysdate, "Day", "nls_date_language=Dutch")
  3  from   dual;
TO_CHAR(S TO_CHAR(S
--------- ---------
Maandag   Maandag
SQL>
SQL>

to_char(sysdate,"hh24:mi:ss") as time

  
SQL> select sysdate                       as today
  2  ,      to_char(sysdate,"hh24:mi:ss") as time
  3  ,      to_char(to_date("01/01/2006","dd/mm/yyyy")
  4                ,""is on "Day") as new_year_2006
  5  from dual;
TODAY      TIME     NEW_YEAR_2006
---------- -------- ---------------
26-10-2009 10:03:46 is on Zondag
SQL>
SQL>

Use to_char function to init an text value

   
SQL>
SQL> DECLARE
  2     lv_current_date      DATE := SYSDATE;
  3     lv_current_month_txt VARCHAR(3) := TO_CHAR(SYSDATE, "MON");
  4  BEGIN
  5     DBMS_OUTPUT.PUT_LINE("Current Date:  " ||lv_current_date);
  6     DBMS_OUTPUT.PUT_LINE("Current Month: " ||lv_current_month_txt);
  7  END;
  8  /
Current Date:  19-JUN-08
Current Month: JUN
PL/SQL procedure successfully completed.
SQL>
SQL>