Conversion Functions in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Conversion Functions in Oracle with Examples

In this article, I am going to discuss Conversion Functions in Oracle with Examples. Please read our previous article where we discussed Date Functions in Oracle with Examples. At the end of this article, you will understand the following Oracle Conversion Functions with examples.

1. TO_CHAR() Function in Oracle
2. TO_DATE() Function in Oracle

Conversion Functions in Oracle

In some cases, the Oracle Server uses data of one type where it expects data of a different data type. This can happen when the Server automatically converts the data to the expected data type. This data type conversion can be done implicitly by the Server, or explicitly by the user. As a user, we can use the following two functions to convert explicitly.

1. TO_CHAR()
2. TO_DATE()

Examples to understand Conversion Functions in Oracle:

We are going to use the following EMP table to understand the Oracle Conversion Functions with examples.

Please use the below SQL script to create and populate the EMP table with the required data.

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

TO_CHAR() Function in Oracle:

The TO_CHAR function in Oracle is used to convert a DateTime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE data type to a value of VARCHAR2 datatype in a specified format. The following is the syntax to use the TO_CHAR function in oracle.

Syntax: TO_CHAR({ Datetime | Interval } [, fmt [, ‘nlsparam’ ] ])

Parameters:

1. Datetime / Interval: A date or number that will be converted to a string.
2. fmt: It is Optional. This specifies the format that will be used to convert the value to a string. If no format (fmt) is specified, then date is converted to VARCHAR2 value as follows:
   1. DATE values are converted to values in the default date format.
   2. TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
   3. TIMESTAMP WITH TIME ZONE values is converted to values in the default timestamp with time zone format.
3. nlsparam: It Specifies the language in which month and day names and abbreviations are returned. This argument can have this form: ‘NLS_DATE_LANGUAGE = language’

YEAR FORMATS:

1. YYYY – 2020
2. YY – 20
3. YEAR – TWENTY TWENTY
4. CC – CENTUARY 21
5. AD / BC – AD YAER / BC YEAR

Example:
SELECT TO_CHAR(SYSDATE,’YYYY YY YEAR CC AD’) FROM DUAL;
Output:

Example: Display Employees who are joined in the Year 1982 from the EMP table by using TO_CHAR() function.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YYYY’)=1982;
Output:

Example: Display employees who are joined in years 1980,1982,1987 by using TO_CHAR() function.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YYYY’) IN(1980,1982,1987);
Output:

MONTH FORMAT in ORACLE:

1. MM – Month number
2. MON – First three chars from month spelling
3. MONTH – Full name of month

Example:
SELECT TO_CHAR(SYSDATE,’MM MON MONTH’) FROM DUAL;
Output:

Example:
SELECT TO_CHAR(SYSDATE,’mm mon month’) FROM DUAL;
Output:

Example: Display employees who are joined in Feb, May, Dec months by using TO_CHAR() function.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MM’) IN(02,05,12);
Output:

Example: Display employees who are joined in Feb 1981 by using TO_CHAR() function.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MMYYYY’)=’021981′;
Output:

DAY Formats in Oracle:

1. DDD – DAY OF THE YEAR.
2. DD – DAY OF THE MONTH.
3. D – DAY OF THE WEEK
   1. SUN – 1
   2. MON – 2
   3. TUE – 3
   4. WEN – 4
   5. THU – 5
   6. FRI – 6
   7. SAT – 7
4. DAY – FULL NAME OF THE DAY
5. DY – FIRST THREE CHAR’s OF DAY SPELLING

Example
SELECT TO_CHAR(SYSDATE,’DDD DD D DAY DY’) FROM DUAL;
Output:

Example: Display employees who are joined on “MONDAY” by using TO_CHAR() function.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’DAY’)=’MONDAY’;

Example: Display employee on which day employees are joined.
SELECT ENAME||’ ‘||’JOINED ON’||’ ‘||TO_CHAR(HIREDATE,’DAY’) FROM EMP;

Note: In oracle whenever we use TO_CHAR() and also within TO_CHAR() when we use day/month format then the oracle server internally allocates some extra memory for the day/month format of data. To overcome the above problem that is to remove extra memory which was allocated by the oracle server then we use a pre-define specifier is called “fm” (fill mode).

Example:
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’FMDAY’)=’FRIDAY’;

Quarter Format in Oracle:

Q – ONE DIGIT QUATER OF THE YEAR
1 – JAN – MAR
2 – APR – JUN
3 – JUL – SEP
4 – OCT – DEC

Example:
SELECT TO_CHAR(SYSDATE,’Q’) FROM DUAL;
Output:

Example: Display the employees who are joined in 2nd quarter of 1981.
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YYYY’)=’1981′ AND TO_CHAR(HIREDATE,’Q’)=2;
Output:

Week Format in Oracle:

1. WW – WEEK OF THE YEAR
2. W – WEEK OF MONTH

Example:
SELECT TO_CHAR(SYSDATE,’WW W’) FROM DUAL;
Output:

Time Format in Oracle:

1. HH – HOUR PART
2. HH24 – 24 hrs FROMAT
3. MI – MINUTE PART
4. SS – SECONDS PART
5. AM / PM – AM TME (OR) PM TIME

Example:
SELECT TO_CHAR(SYSDATE,’HH:MI:SS AM’) FROM DUAL;
Output:

TO_DATE() Function in Oracle:

The TO_DATE() function in oracle is used to convert char type to oracle date format type. The following is the syntax to use the TO_DATE() function in oracle.

Syntax: TO_DATE(STRING[,FROMAT])

Example:
SELECT TO_DATE(’08/AUGUST/2020′) FROM DUAL;
Output:

Example:
SELECT TO_DATE(’08-AUG-2020′)+10 FROM DUAL;
Output:

In the next article, I am going to discuss Multiple Row Functions in Oracle with Examples. Here, in this article, I try to explain Conversion Functions in Oracle with Examples and I hope you enjoy this Conversion Functions in Oracle with Examples article.