Oracle Conversion Functions
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose This page is a collection of built-in Oracle Database functions used to convert data types.

Additional undocumented string related functions can be found on the Undocumented Oracle page also linked at page bottom.
Note
Have you seen the Functions page? If not ... Click Here ... for information on all Oracle functions
Dependencies
STANDARD    
 
ASCIISTR
Converts An ASCII String To An ASCII String In The Database's Character Set ASCIISTR(ch IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET ch%CHARSET;
SELECT ASCIISTR(CHR(128) || 'Hello' || CHR(255))
FROM dual;
 
BFILENAME
Returns a BFILE from a combination of a directory and a file name BFILENAME(directory IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;
DECLARE
 src_file BFILE;
BEGIN
  src_file := bfilename('CTEMP', 'myfile.txt');
END;
/
 
BIN_TO_NUM
Converts a bit vector to a number BIN_TO_NUM(<value>,<value>,....) RETURN NUMBER;
SELECT BIN_TO_NUM(1, 0, 1, 0) FROM dual;
 
CAST
Converts one built-in datatype or collection-typed value into another built-in datatype or collection-typed value CAST(<string_or_column> AS <DATATYPE>)
SELECT CAST(15402 AS VARCHAR2(30))
FROM dual;
 
CHARTOROWID
Converts a value from CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to ROWID datatype CHARTOROWID(str IN VARCHAR2) RETURN ROWID;
conn hr/hr@pdbdev

SELECT rowid
FROM employees;

SELECT last_name
FROM employees
WHERE ROWID = CHARTOROWID('AAAQvRAAFAAAABYAAp');
 
COMPOSE
Convert a string in any data type to a Unicode string

Overload 1		 COMPOSE(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET ch%charset;

Unistring Value Resulting character
unistr('\0300') grave accent (`)
unistr('\0301') acute accent (´)
unistr('\0302') circumflex (ˆ)
unistr('\0303') tilde (~)
unistr('\0308') umlaut (¨)
SELECT 'ol' || COMPOSE('e' || UNISTR('\0301')) FROM dual;
 
CONVERT
Converts a character string from one character set to another

Overload 1		 CONVERT(src IN VARCHAR2 CHARACTER SET ANY_CS, destcset IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET srcstr%charset;
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E', 'US7ASCII')
FROM dual;
Overload 2 CONVERT(
src      IN VARCHAR2 CHARACTER SET ANY_CS,
destcset IN VARCHAR2,
srccset  IN VARCHAR2)
RETURN VARCHAR2 CHARACTER SET srcstr%charset;
SELECT CONVERT('Ä Ê Í Ó Ø A B C D E','US7ASCII', 'WE8ISO8859P1')
FROM dual;
Overload 3 CONVERT(srcstr IN CLOB CHARACTER SET ANY_CS, dstcsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;
TBD
Overload 4 CONVERT(
srcstr IN CLOB CHARACTER SET ANY_CS,
dstcsn IN VARCHAR2,
srccsn IN VARCHAR2)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;
TBD
 
DECOMPOSE
Converts a unicode string to a string DECOMPOSE(
ch      IN VARCHAR2 CHARACTER SET ANY_CS,
canmode IN VARCHAR2 DEFAULT 'CANONICAL')
RETURN VARCHAR2 CHARACTER SET ch%charset;
SELECT DECOMPOSE('ol' || COMPOSE('e' || UNISTR('\0301')))
FROM dual;
 
HEXTORAW
Converts char containing hexadecimal digits in the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 character set to a raw value HEXTORAW(c IN VARCHAR2) RETURN RAW;
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
raw_col RAW(10));

desc test

INSERT INTO test VALUES (HEXTORAW('7D'));

SELECT * FROM test;
 
NUMTODSINTERVAL
Converts a number to an INTERVAL DAY TO SECOND literal NUMTODSINTERVAL(
numerator IN NUMBER,
units     IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL DAY TO SECOND;
SELECT NUMTODSINTERVAL(120, 'DAY') FROM dual;

SELECT NUMTODSINTERVAL(1200, 'HOUR') FROM dual;

SELECT NUMTODSINTERVAL(12000, 'MINUTE') FROM dual;

SELECT NUMTODSINTERVAL(120000, 'SECOND') FROM dual;
 
NUMTOYMINTERVAL
Converts n to an INTERVAL YEAR TO MONTH literal NUMTOYMINTERVAL(
numerator IN NUMBER,
units     IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL YEAR TO MONTH;
conn hr/hr@pdbdev

SELECT last_name, hire_date, salary, SUM(salary)
OVER (ORDER BY hire_date
RANGE NUMTOYMINTERVAL(1, 'YEAR') PRECEDING) AS t_sal
FROM employees;
 
RAWTOHEX
Converts raw to a character value containing its hexadecimal equivalent

See also UTL_RAW linked at page bottom		 RAWTOHEX(r IN RAW) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
raw_col RAW(10));

desc test

INSERT INTO test VALUES (HEXTORAW('7D'));

SELECT * FROM test;
SELECT RAWTOHEX(raw_col) HEXVAL
FROM test;
 
RAWTONHEX
Converts raw to an NVARCHAR2 character value containing its hexadecimal equivalent RAWTOHEX(r IN RAW) RETURN NVARCHAR2;
conn uwclass/uwclass@pdbdev

col dumpcol format a30

SELECT RAWTONHEX(raw_col) HEXVAL, dump(raw_col) dumpcol
FROM test;
 
REFTOHEX
Converts argument expr to a character value containing its hexadecimal equivalent. expr must return a REF REFTOHEX(<expr>);
conn oe/oe@pdbdev

CREATE TABLE warehouse_table OF warehouse_typ
(PRIMARY KEY (warehouse_id));

CREATE TABLE location_table (
location_number NUMBER,
building        REF warehouse_typ SCOPE IS warehouse_table);

INSERT INTO warehouse_table VALUES (1, 'Downtown', 99);

INSERT INTO location_table SELECT 10, REF(w)
FROM warehouse_table w;

SELECT REFTOHEX(building) FROM location_table;

DROP TABLE warehouse_table PURGE;
 
ROWIDTOCHAR
Converts a rowid value to VARCHAR2 datatype ROWIDTOCHAR(str IN ROWID) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

SELECT COUNT(*)
FROM servers;

SELECT rowid
FROM servers
WHERE rownum < 11;

SELECT ROWID
FROM servers
WHERE ROWIDTOCHAR(ROWID) LIKE '%AAB%';
 
ROWIDTONCHAR
Converts a rowid value to NVARCHAR2 datatype ROWIDTONCHAR(str IN ROWID) RETURN NVARCHAR2;
See ROWIDTOCHAR demo above
 
SCN_TO_TIMESTAMP
Returns the approximate Timestamp for an SCN

Source dbmstran.sql		 SCN_TO_TIMESTAMP(query_scn IN NUMBER) RETURN TIMESTAMP
IS EXTERNAL NAME "ktfexscntot"
WITH CONTEXT
PARAMETERS(context, query_scn OCINUMBER, RETURN)
LIBRARY DBMS_TRAN_LIB;
/
col current_scn format 99999999999999999999

SELECT current_scn
FROM v$database;

SELECT SCN_TO_TIMESTAMP(10912156206286)
FROM dual;
 
TIMESTAMP_TO_SCN
Returns the approximate SCN for a timestamp

Source dbmstran.sql		 TIMESTAMP_TO_SCN(query_time IN TIMESTAMP) RETURN NUMBER
IS EXTERNAL NAME "ktfexttoscn"
WITH CONTEXT
PARAMETERS(context, query_time OCIDATETIME, RETURN)
LIBRARY DBMS_TRAN_LIB;
/
col current_scn format 99999999999999999999

SELECT current_scn
FROM v$database;

SELECT timestamp_to_scn(SYSTIMESTAMP) CURRENT_SCN
FROM dual;

CURRENT_SCN
-----------
   19431615
 
TO_ACLID: Click on the link at page bottom
 
TO_APPROX_COUNT_DISTINCT
Takes as its input a detail containing information about an approximate distinct value count, and converts it to a NUMBER value TO_APPROX_COUNT_DISTINCT(<detail>) RETURN NUMBER;
SELECT year, month, day,
to_approx_count_distinct(daily_detail) AS INVENTORY
FROM daily_prod_count_mv
ORDER BY 1, 2, 3;

 YEAR MONTH DAY INVENTORY
----- ----- --- ---------
 2018     1   1       124
 2018     1   2       125
 2018     1   3       111
 2018     1   4       134
 2018     1   5       110
 2018     1   6       108
 2018     1   7       137
 2018     1   8       126
 2018     1   9       125
 2018     1  10       138
....
 
TO_APPROX_PERCENTILE
Takes as its input a detail containing approximate percentile information, a percentile value, and a sort specification, and returns an approximate interpolated value that would fall into that percentile value with respect to the sort specification. TO_APPROX_PERCENTILE(<detail> <expression> , '<datatype'>, ['<ASC | CONFIDENCE | DESC | ERROR_RATE>'] RETURN NUMBER;
SELECT country, state, city,
TO_APPROX_PERCENTILE(city_detail, .25, 'NUMBER') "25th Percentile",
TO_APPROX_PERCENTILE(city_detail, .50, 'NUMBER') "50th Percentile",
TO_APPROX_PERCENTILE(city_detail, .75, 'NUMBER') "75th Percentile"
FROM amt_sold_by_city_mv
ORDER BY 1, 2, 3;

COUNTRY STATE        CITY 25th      Percentile 50th Percentile 75th Percentile
------- ------------ -------------- --------------- --------------- ----------
  52769 Kuala Lumpur Kuala Lumpur             19.29           38.10      53.84
  52769 Penang       Batu Ferringhi           21.51           42.09      57.26
  52769 Penang       Georgetown               19.15           33.25      56.12
  52769 Selangor     Klang                    18.08           32.06      51.29
  52769 Selangor     Petaling Jaya            19.29           35.43      60.20
 
TO_BINARY_DOUBLE
Converts a Value to the BINARY_DOUBLE Data Type
Overload 1		 TO_BINARY_DOUBLE(right IN BINARY_DOUBLE) RETURN BINARY_DOUBLE;
See TO_NUMBER Demo Below
Overload 2 TO_BINARY_DOUBLE(
left   IN VARCHAR2 SET ANY_CS, FORMAT,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_DOUBLE;
See TO_NUMBER Demo Below
Overload 3 TO_BINARY_DOUBLE(
left   IN VARCHAR2 SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
params IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_DOUBLE;
See TO_NUMBER Demo Below
 
TO_BINARY_FLOAT
Converts a Value to the BINARY_FLOAT Data Type TO_BINARY_FLOAT(right IN BINARY_FLOAT) RETURN BINARY_FLOAT;
See TO_NUMBER Demo Below
Overload 2 TO_BINARY_FLOAT(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_FLOAT;
See TO_NUMBER Demo Below
Overload 3 TO_BINARY_FLOAT(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
params IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN BINARY_FLOAT;
See TO_NUMBER Demo Below
 
TO_BLOB
Converts a RAW value into a BLOB

Overload 1		 TO_BLOB(right IN RAW) RETURN BLOB;
DECLARE
 b BLOB;
BEGIN
  b := TO_BLOB('0F0F0F');
END;
/
Overload 2 TO_BLOB(left IN BFILE) RETURN BLOB;
TBD
 
TO_CHAR
Convert Datatype To String
Overload 1		 TO_CHAR(right IN VARCHAR2) RETURN VARCHAR2;
SELECT TO_CHAR('Dan Morgan') FROM dual;
Overload 2 TO_CHAR(left IN DATE, right IN VARCHAR2) RETURN VARCHAR2;
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH:MI:SS') FROM dual;
Overload 3
Convert NUMBER to String		 TO_CHAR(left IN NUMBER) RETURN VARCHAR2;
SELECT TO_CHAR(123) FROM dual;
Overload 3
Convert NUMBER to HEX		 TO_CHAR(left IN NUMBER, right IN VARCHAR2) RETURN VARCHAR2;
SELECT TO_CHAR(1048576,'XXXXXXXX')
FROM dual;
Overload 4 TO_CHAR(left IN MLSLABEL, right IN VARCHAR2) RETURN VARCHAR2;
TBD
Converts DATE and  TIMESTAMP to VARCHAR2 with the specified format

The "X" in the ROUND and TRUNC column indicates that these symbols with these functions

Overload 5		 TO_CHAR(left IN date, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;
-- before running these demos
conn uwclass/uwclass@pdbdev

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

Symbol Description ROUND TRUNC

AD
A.D.		 Returns "AD" if the year is greater than 0000: Otherwise "BC"
X		  
SELECT TO_CHAR(TO_DATE('31-DEC-0001')+1000, 'BC') FROM dual;

TO
--
AD

SELECT TO_CHAR(TO_DATE('31-DEC-0398')-400, 'B.C.') FROM dual;

TO_C
----
A.D.
AM
A.M.		 Returns "AM" if the time is less than noon: Otherwise "PM" X  
SELECT TO_CHAR(TO_DATE('01-JAN-2020'), 'AM') FROM dual;

TO
--
AM

BC
B.C.		 Returns "BC" if the year is less than 0000: Otherwise "AD"
X		  
SELECT TO_CHAR(TO_DATE('31-DEC-0001')-1000, 'BC') FROM dual;

TO
--
BC

SELECT TO_CHAR(TO_DATE('31-DEC-0001')-400, 'BC') FROM dual;

TO
--
00
CC Century (Same as SCC) X X
SELECT TO_CHAR(sysdate, 'CC') FROM dual;

TO_CHAR(sysdate,'CC')
---------------------------------------------
21
D Starting day of the week X X
SELECT sysdate, TO_CHAR(sysdate, 'D') FROM dual;

sysdate               T
--------------------  -
09-MAR-2020 16:28:54  3
DD Day X X
SELECT sysdate, TO_CHAR(sysdate, 'DD') FROM dual;

sysdate               TO
--------------------  --
09-MAR-2020 16:29:50  09
DDD Day X X
SELECT TO_CHAR(sysdate, 'DDD') FROM dual;

TO_CHAR(sysdate,'DDD')
-----------------------
069
DAY Starting day of the week X X
SELECT sysdate, TO_CHAR(sysdate, 'DAY') FROM dual;

sysdate              TO_CHAR(S
-------------------- ---------
09-APR-2020 16:30:04 MONDAY
DL Day and long date format X  
SELECT TO_CHAR(sysdate, 'DL') FROM dual;

TO_CHAR(sysdate,'DL')
-----------------------------
Monday, March 09, 2020
DS Short date format  X  
SELECT TO_CHAR(sysdate, 'DS') FROM dual;

TO_CHAR(SY
----------
3/9/2020
DY Current day of the week X X
SELECT sysdate, TO_CHAR(sysdate, 'DY') FROM dual;

sysdate               TO_
--------------------  ---
09-MAR-2020 16:30:57  MON
E Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).  X  
TBD
EE Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).  X  
TBD

FM		 Returns a value with no leading or trailing blanks. Not my experience.
X		  
SELECT TO_CHAR(sysdate, 'Day, Month DD, YYYY') FROM dual;

TO_CHAR(SYSDATE,'DAY,MONTHDD,
-----------------------------
Monday , March    09, 2020

SELECT TO_CHAR(sysdate, 'FMDay, Month DD, YYYY') FROM dual;

TO_CHAR(sysdate,'FMDAY,MONTHD
-----------------------------
Monday, March 9, 2020

FX		 Requires exact matching between the character data and the format model
X		  
SELECT TO_CHAR(sysdate, 'Day, Month DD, YYYY') FROM dual;

TO_CHAR(SYSDATE,'DAY,MONTHDD,
-----------------------------
Monday , March 09, 2020

SELECT TO_CHAR(sysdate, 'FXDay, Month DD, YYYY') FROM dual;

TO_CHAR(SYSDATE,'FMDAY,MONTHD
-----------------------------
Monday , March 09, 2020

HH		 Truncate Date-Time to Hours
X
X
SELECT sysdate, TO_CHAR(sysdate, 'HH') FROM dual;

SYSDATE              TO
-------------------- --
09-MAR-2020 16:32:19 12

SELECT sysdate, TRUNC(sysdate, 'HH') FROM dual;

SYSDATE              TRUNC(SYSDATE,'HH')
-------------------- --------------------
09-MAR-2020 16:32:33 09-MAR-2020 16:00:00
HH12 Hours (12 hour clock) X  
SELECT sysdate, TO_CHAR(sysdate, 'HH12') FROM dual;

SYSDATE              TO
-------------------- --
09-MAR-2020 16:32:56 04
HH24 Hours (24 hour clock) X  
SELECT sysdate, TO_CHAR(sysdate+5/24, 'HH24') FROM dual;

SYSDATE              TO
-------------------- --
09-MAR-2020 16:33:12 16
I ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'I') FROM dual;

SYSDATE               T
--------------------  -
09-MAR-2020 16:33:37  5
IW Same day of the week as the first day of the ISO year X X
SELECT sysdate, TO_CHAR(sysdate, 'IW') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2020 22:08:57  08
IY ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IY') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2020 22:10:04  20
IYY ISO 3 Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IYY') FROM dual;

SYSDATE               TO_
--------------------  ---
17-FEB-2020 20:51:59  020
IYYY ISO Year X X
SELECT sysdate, TO_CHAR(sysdate, 'IYYY') FROM dual;

SYSDATE               TO_C
--------------------  ----
17-FEB-2020 20:51:36  2020
J Julian Day X X
SELECT sysdate, TO_CHAR(sysdate, 'J') FROM dual;

SYSDATE               TO_CHAR
--------------------  -------
24-MAR-2020 00:14:17  2458933
JSP Julian Day    
SELECT TO_CHAR(TO_DATE(4242,'J'), 'JSP') as converted_form FROM dual;

CONVERTED_FORM
-----------------------------------
FOUR THOUSAND TWO HUNDRED FORTY-TWO
MI Minutes X X
SELECT sysdate, TO_CHAR(sysdate, 'MI') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2020 11:11:47  11
MM Month (rounds up on the sixteenth day) X X
SELECT sysdate, TO_CHAR(sysdate, 'MM') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2020 11:12:07  02
MON Month abreviation X X
SELECT sysdate, TO_CHAR(sysdate, 'MON') FROM dual;

SYSDATE               TO_
--------------------  ---
07-JAN-2020 20:26:09  JAN
MONTH Month name spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'MONTH') FROM dual;

SYSDATE               TO_CHAR(S
--------------------  ---------
07-JAN-2020 20:26:38  JANUARY

PM
P.M.		 Returns "PM" if the time is after noon: Otherwise "AM"
X		  
SELECT TO_CHAR(TO_DATE('01-JAN-2020') + 3/24, 'AM') FROM dual;

TO
--
AM

SELECT TO_CHAR(TO_DATE('01-JAN-2020') + 13/24, 'PM') FROM dual;

TO
--
PM

Q		 Quarter
X
X
SELECT sysdate, TO_CHAR(sysdate, 'Q') FROM dual;

SYSDATE               T
--------------------  -
24-MAR-2020 08:51:22  1

SELECT sysdate, TRUNC(sysdate, 'Q') FROM dual;

SYSDATE               TRUNC(SYSDATE,'Q')
--------------------  --------------------
24-MAR-2020 08:51:38  01-JAN-2020 00:00:00
RM Roman Numeral Month X X
SELECT sysdate, TO_CHAR(sysdate, 'RM') FROM dual;

SYSDATE               TO_C
--------------------  ----
24-MAR-2020 08:51:56  III
RR Allows storage of 20th century dates in the 21st century using only two digits X X
SELECT sysdate, TO_CHAR(sysdate, 'RR') FROM dual;

SYSDATE               TO
--------------------  --
24-MAR-2020 08:52:08  20
RRRR Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. X X
SELECT sysdate, TO_CHAR(sysdate, 'RRRR') FROM dual;

SYSDATE               TO_C
--------------------  ----
07-JAN-2020 20:54:45  2020
SCC Century (same as CC) X X
SELECT sysdate, TO_CHAR(sysdate, 'SCC') FROM dual;

SYSDATE               TO_
--------------------  ---
24-MAR-2020 08:52:42 21

SP		 Spelled out
X		  
SQL> SELECT sysdate, TO_CHAR(sysdate, 'SP') FROM dual;
                                      *
ERROR at line 1:
ORA-01821: date format not recognized

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'MMSP') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'MMSP')
-------------------- ----------------------------------
24-MAR-2020 08:53:15 THREE

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'DDSP') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'DDSP')
-------------------- ----------------------------------
24-MAR-2020 08:53:35 TWENTY-FOUR

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'YYSP') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'YYSP')
-------------------- ----------------------------------
24-MAR-2020 08:54:12 TWENTY

SPTH		 Spelled out in ordinal format
X		  
SQL> SELECT sysdate, TO_CHAR(sysdate, 'SPTH') FROM dual;
                                      *
ERROR at line 1:
ORA-01821: date format not recognized

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'MMSPTH') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'MMSPTH')
-------------------- ----------------------------------
24-MAR-2020 08:55:19 THIRD

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'DDSPTH') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'DDSPTH')
-------------------- ----------------------------------
24-MAR-2020 08:55:37 TWENTY-FOURTH

SQL> ed
Wrote file afiedt.buf

1* SELECT sysdate, TO_CHAR(sysdate, 'YYSPTH') FROM dual
SQL> /

SYSDATE              TO_CHAR(SYSDATE,'YYSPTH')
-------------------- ----------------------------------
24-MAR-2020 08:56:02 TWENTIETH
SS Seconds X  
SELECT sysdate, TO_CHAR(sysdate, 'SS') FROM dual;

SYSDATE               TO
--------------------  --
17-FEB-2020 11:25:30  30
SSSSS Seconds past midnight X  
SELECT sysdate, TO_CHAR(sysdate, 'SSSSS') FROM dual;

SYSDATE               TO_CH
--------------------  -----
24-MAR-2020 08:56:37 32197
SYEAR Year spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'SYEAR') FROM dual;

SYSDATE               TO_CHAR(SYSDATE,'SYEAR')
--------------------  ------------------------
07-JAN-2019 20:48:10  TWENTY NINETEEN
SYYYY Four digit year. Prefixes BC with a minus sign X X
SELECT sysdate, TO_CHAR(sysdate, 'SYYYY') FROM dual;

SYSDATE               TO_CH
--------------------  -----
07-JAN-2020 21:15:33  2020
TS Short time format X  
SELECT sysdate, TO_CHAR(sysdate, 'TS') FROM dual;

SYSDATE               TO_CHAR(SYS
--------------------  -----------
07-JAN-2020 21:23:48  9:23:48 PM
W Week number in the month X X
SELECT sysdate, TO_CHAR(sysdate, 'W') FROM dual;

SYSDATE               T
--------------------  -
24-MAR-2020 08:57:39 4
WW Week of the year X X
SELECT sysdate, TO_CHAR(sysdate, 'WW') FROM dual;

SYSDATE               TO
--------------------  --
24-MAR-2020 08:57:54 12
X Local Radix Character X  
SELECT TO_CHAR(SYSTIMESTAMP, 'DD-MON-YYYY HH:MM:SSXFF') FROM dual;
                                  *
TO_CHAR(SYSTIMESTAMP,'DD-MON-Y
------------------------------
24-MAR-2020 08:03:08.236000
Y One Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'Y') FROM dual;

SYSDATE               T
--------------------  -
17-FEB-2019 11:36:51  9
YEAR Year name spelled out X X
SELECT sysdate, TO_CHAR(sysdate, 'YEAR') FROM dual;

SYSDATE              TO_CHAR(SYSDATE,'YEAR')
-------------------- -----------------------
17-FEB-2020 07:01:42 TWENTY TWENTY
YY Two Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YY') FROM dual;

SYSDATE               TO
--------------------  --
07-MAR-2020 21:04:20  20
YYY Three Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YYY') FROM dual;

SYSDATE               TO_
--------------------  ---
07-MAR-2020 21:04:32  020
YYYY Four Digit Year X X
SELECT sysdate, TO_CHAR(sysdate, 'YYYY') FROM dual;

SYSDATE               TO_C
--------------------  ----
07-MAR-2020 21:04:42  2020

CREATE TABLE t (
datecol1 DATE,
datecol2 DATE);

INSERT INTO t (datecol1, datecol2) VALUES (SYSDATE, SYSDATE);

col col1 format a30
col col2 format a20
col col3 format a20
col "Financial Quarter" format a20

SELECT TO_CHAR(datecol1, 'DAY-MONTH-YY') COL1,
       TO_CHAR(datecol2, 'D-MM/YYYY HH24:MI:SS') COL2,
       TO_CHAR(datecol2, 'YYYY-MON-DD HH24') COL3,
       TO_CHAR(datecol2, 'Q') "Financial Quarter"
FROM t;

COL1                COL2                 COL3            Financial Quarter
------------------- -------------------- --------------- -----------------
TUESDAY -MARCH -20   3-03/2020 09:00:10  2020-MAR-24 09  1
Demo of FM Formatting From Tom Kyte (Oracle Magazine 3-4/2004) SELECT TO_CHAR(dt, 'HH:MI AM') A,
       TO_CHAR(dt, 'FMHH:MI AM') B,
       TO_CHAR(dt, 'FMHHFM:MI AM') C
FROM (SELECT TO_DATE('09:01 am', 'HH:MI AM') dt FROM dual);

A         B        C
--------- -------- --------
09:01 AM  9:1 AM   9:01 AM
Overload 6 TO_CHAR(left IN NUMBER, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 7 TO_CHAR(left IN TIME, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 8 TO_CHAR(left IN TIME, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 9 TO_CHAR(left IN TIME WITH TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 10 TO_CHAR(left IN TIME WITH TIME ZONE, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 11 TO_CHAR(left IN TIMESTAMP, format IN VARCHAR2, parms IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 12 TO_CHAR(left IN TIMESTAMP, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 13 TO_CHAR(left IN TIMESTAMP WITH TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 14 TO_CHAR(left IN TIMESTAMP WITH TIME ZONE, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 15 TO_CHAR(left IN TIMESTAMP WITH LOCAL TIME ZONE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 16 TO_CHAR(left IN TIMESTAMP WITH LOCAL TIME ZONE, format IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 17 TO_CHAR(left IN INTERVAL YEAR TO MONTH, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 18 TO_CHAR(left IN INTERVAL YEAR TO MONTH, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 19 TO_CHAR(left IN INTERVAL DAY TO SECOND, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 20 TO_CHAR(left IN INTERVAL DAY TO SECOND, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 21 TO_CHAR(left IN BINARY_FLOAT, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 22 TO_CHAR(left IN BINARY_DOUBLE, format IN VARCHAR2) RETURN VARCHAR2;
TBD
Overload 23 TO_CHAR(left IN BINARY_FLOAT, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 24 TO_CHAR(left IN BINARY_DOUBLE, format IN VARCHAR2, parms IN VARCHAR2)
RETURN VARCHAR2;
TBD
Overload 25 TO_CHAR(left IN BLOB, csid IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
TBD
Overload 26 TO_CHAR(left IN BFILE, csid IN NUMBER DEFAULT NULL)
RETURN VARCHAR2;
TBD
 
TO_CLOB
Overload 1 TO_CLOB(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN CLOB;
SELECT TO_CLOB('Some string value')
FROM dual;
Overload 2 TO_CLOB(cl IN CLOB CHARACTER SET ANY_CS) RETURN CLOB;
SELECT TO_CLOB('Some string value')
FROM dual;
Overload 3 TO_CLOB(left IN BLOB, csid IN NUMBER DEFAULT NULL) RETURN CLOB;
TBD
Overload 4 TO_CLOB(left IN BFILE, csid IN NUMBER DEFAULT NULL) RETURN CLOB;
TBD
 
TO_DATE
Overload 1Convert a string with default format to a date
Overload 1		 TO_DATE(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN DATE;
SELECT TO_DATE('01-JAN-2020') FROM dual;

TO_DATE('01-JAN-2020
--------------------
01-JAN-2020 00:00:00
Convert a string with non-default format to a date
Overload 2		 TO_DATE(
left  IN VARCHAR2 CHARACTER SET ANY_CS,
right IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN DATE;
SELECT TO_DATE('01/01/2020', 'MM/DD/YYYY') FROM dual;

TO_DATE('01/01/2020'
--------------------
01-JAN-2020 00:00:00
Convert A String With A Non-Default Format And Specify The Language
Overload 2		 SELECT TO_DATE('March 15, 2020, 11:03 A.M.', 'MONTH DD, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM dual;

TO_DATE('MARCH15,202
--------------------
15-MAR-2020 11:03:00
Convert A String To 24 Hour Time
Overload 2		 SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS')
FROM dual;

TO_CHAR(SYSDATE,'
-----------------
03/24/20 09:08:31
Overload 3 TO_DATE(left IN NUMBER, right IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN DATE;
TBD
Convert A String With A Non-Default Format And Specify The Language

Overload 4		 TO_DATE(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms  IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN DATE;
conn uwclass/uwclass@pdbdev

ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

Session altered.

SELECT TO_DATE('January 07, 2020, 11:03 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE(
--------
20-01-07

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';

Session altered

SELECT TO_DATE('January 07, 2020, 11:03 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

TO_DATE('
---------
07-JAN-20
 
TO_DSINTERVAL
Converts A String To An INTERVAL DAY TO SECOND DataType

Overload 1		 TO_DSINTERVAL(
right IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN INTERVAL DAY TO SECOND;
conn hr/hr@pdbdev

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '2020-01-01';
Overload 2 TO_DSINTERVAL(
right IN VARCHAR2 CHARACTER SET ANY_CS,
parms IN VARCHAR2 CHARACTER SET RIGHT%CHARSET)
RETURN INTERVAL DAY TO SECOND;
conn hr/hr@pdbdev

SELECT employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00') <= DATE '2020-01-01';
 
TO_LOB
Converts LONG or LONG RAW values in the column long_column to LOB values TO_LOB(long_column) RETURN LOB;
desc user_triggers

CREATE TABLE lobtest (
testcol CLOB);

INSERT INTO lobtest
SELECT TO_LOB(trigger_body)
FROM user_triggers;
 
TO_MULTI_BYTE
Returns char with all of its single-byte characters converted to their corresponding multibyte characters TO_MULTI_BYTE(c IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET c%CHARSET;
-- must be run in a UTF8 database to see the difference
SELECT dump('A') FROM dual;

DUMP('A')
----------------
Typ=96 Len=1: 65

SELECT dump(TO_MULTI_BYTE('A')) FROM dual;

DUMP(TO_MULTI_BYTE('A'))
------------------------
Typ=1 Len=3: 239,188,161
 
TO_NCHAR
Overload 1 TO_NCHAR(right IN NVARCHAR2) RETURN NVARCHAR2;
SELECT TO_NCHAR('ABC') FROM dual;

TO_
---
ABC
Overload 2 TO_NCHAR(left IN DATE, format IN NVARCHAR2) RETURN NVARCHAR2;
conn oe/oe@pdbdev

SELECT TO_NCHAR(order_date)
FROM orders
WHERE order_status > 9;
Overload 3 TO_NCHAR(left IN NUMBER, format IN NVARCHAR2) RETURN NVARCHAR2;
SELECT TO_NCHAR(1048576) FROM dual;

TO_NCHA
-------
1048576
Overload 4 TO_NCHAR(left IN DATE, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;
conn oe/oe@pdbdev

SELECT TO_NCHAR(order_date, 'YYYY-MON-DAY')
FROM orders
WHERE order_status > 9;
Overload 5 TO_NCHAR(left IN NUMBER, format IN NVARCHAR2, parms IN NVARCHAR2) RETURN NVARCHAR2;
TBD
Overload 6 TO_NCHAR(left IN BINARY_FLOAT, format IN NVARCHAR2) RETURN NVARCHAR2;
TBD
Overload 7 TO_NCHAR(left IN BINARY_DOUBLE, format IN NVARCHAR2) RETURN NVARCHAR2;
TBD
Overload 8 TO_NCHAR(left IN BINARY_FLOAT, format IN NVARCHAR2, parms IN NVARCHAR2)
RETURN NVARCHAR2;
TBD
Overload 9 TO_NCHAR(left IN BINARY_DOUBLE, format IN NVARCHAR2, parms IN NVARCHAR2)
RETURN NVARCHAR2;
TBD
 
TO_NCLOB
Converts CLOB values in a LOB column or other character strings to NCLOB
Overload 1		 TO_NCLOB(cl IN CLOB CHARACTER SET ANY_CS) RETURN NCLOB;
TBD
Overload 2 TO_NCLOB(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NCLOB;
CREATE TABLE nclob_test(
nclobcol  NCLOB);

desc nclob_test

INSERT INTO nclob_test
(nclobcol)
VALUES
(TO_NCLOB('Convert this text into the NCLOB data type'));
 
TO_NUMBER
Converts a NUMBER to the NUMBER
Overload 1		 TO_NUMBER(right IN NUMBER) RETURN NUMBER;
SELECT TO_NUMBER(123) FROM dual;
Converts a string to the NUMBER data type

Overload 2		 TO_NUMBER(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NUMBER;
conn uwclass/uwclass@pdbdev

CREATE TABLE test (
testcol VARCHAR2(10));

INSERT INTO test VALUES ('12345.67');

SELECT TO_BINARY_DOUBLE(testcol) BIN_DOUBLE, TO_BINARY_FLOAT(testcol) BIN_FLOAT, TO_NUMBER(testcol) NMBR
FROM test;

BIN_DOUBLE  BIN_FLOAT       NMBR
---------- ---------- ----------
1.235E+004 1.235E+004   12345.67
Converts a HEX number to FLOAT

Overload 3		 TO_NUMBER(
right  IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN NUMBER;
SELECT TO_NUMBER('0A', 'XX')
FROM dual;

 TO_NUMBER('0A','XX')
--------------------
                  10

SELECT TO_NUMBER('1F', 'XX')
FROM dual;

TO_NUMBER('1F','XX')
--------------------
                  31

SELECT TO_NUMBER(100000, 'XXXXXXXX')
FROM dual;

TO_NUMBER(100000,'XXXXXXXX')
----------------------------
                     1048576
Overload 4 TO_NUMBER(
right  IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms  IN VARCHAR2 CHARACTER SET LEFT%CHARSET) RETURN NUMBER;
TBD
Strings with trailing signs SELECT TO_NUMBER('20-') FROM dual;
SELECT TO_NUMBER('20-') FROM dual
*
ERROR at line 1:
ORA-01722: invalid number

SELECT TO_NUMBER('20-', '99,999.99MI') FROM dual;

TO_NUMBER('20-','99,999.99MI')
------------------------------
                           -20
 
TO_RAW
Converts BLOB values in a LOB column, or other character strings, to RAW TO_RAW(right IN BLOB) RETURN RAW;
DECLARE
 b BLOB;
 r RAW(32);
BEGIN
  b := TO_BLOB('0F0F0F');

  r := TO_RAW(b);
  dbms_output.put_line(r);
END;
/
0F0F0F

PL/SQL procedure successfully completed.
 
TO_SINGLE_BYTE
Returns char with all of its multibyte characters converted to their corresponding single-byte characters TO_SINGLE_BYTE(c IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET c%CHARSET;
-- must be run in a UTF8 database to see the difference

SELECT TO_SINGLE_BYTE(CHR(15711393))
FROM dual;

T
-
A
 
TO_TIMESTAMP
Overload 1 TO_TIMESTAMP(right IN VARCHAR2) RETURN TIMESTAMP_UNCONSTRAINED;
SELECT TO_TIMESTAMP('01-JUL-2149 11:00:00')
FROM dual;

TO_TIMESTAMP('01-JUL-214911:00:00')
-----------------------------------
01-JUL-49 11.00.00.000000000 AM
Overload 2 TO_TIMESTAMP(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms  IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP;
TBD
Overload 3 TO_TIMESTAMP(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP;
SELECT TO_TIMESTAMP('2020-03-15 11:00:00','YYYY-MM-DD HH:MI:SS')
FROM dual;

TO_TIMESTAMP('2020-03-1511:00:00','YYYY-MM-DDHH:MI:SS')
--------------------------------------------------------
15-MAR-20 11.00.00.000000000 AM
Overload 4 TO_TIMESTAMP(right IN TIMESTAMP WITH TIME ZONE) RETURN TIMESTAMP;
TBD
Overload 5 TO_TIMESTAMP(right IN DATE) RETURN TIMESTAMP
SELECT TO_TIMESTAMP(TO_DATE('01-JAN-2020 01:01:01'))
FROM dual;

TO_TIMESTAMP(TO_DATE('01-JAN-202001:01:01'))
---------------------------------------------
01-JAN-20 01.01.01 AM
Overload 6 TO_TIMESTAMP(arg IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN TIMESTAMP;
TBD
To Timestamp with Time Zone
Symbol Description Round Trunc

FF
[1...9]		 Fractional Seconds
 X		  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF1') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
24-MAR-20 04.16.50.279000 PM -05:00  2

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF3') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
24-MAR-20 04.16.15.225000 PM -05:00  225

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF5') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
24-MAR-20 04.17.19.491000 PM -05:00  49100

SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'FF7') FROM dual;

SYSTIMESTAMP                         TO_CHAR(S
------------------------------------ ---------
24-MAR-20 04.17.43.111000 PM -05:00 1110000
TZD Timestamp with daylight savings time X  
SELECT TO_CHAR(TO_TIMESTAMP_TZ('2020-03-15 13:27:58 CET', 'YYYY-MM-DD HH24:MI:SS TZR'), 'YYYY-MM-DD HH24:MI:SS TZR TZD')
FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('2020-03-1513:27:58CET','YYYY-MM-DD
-----------------------------------------------------------
2020-03-15 13:27:58 CET CET

SELECT TO_TIMESTAMP_TZ('3/15/2020 7:29 AM PDT','MM/DD/YYYY HH:MI AM TZD') FROM dual;

TO_TIMESTAMP_TZ('3/15/20207:29AMPDT','MM/DD/YYYYHH:MIAMTZD')
------------------------------------------------------------
15-MAR-20 07.29.00.000000000 AM -05:00
TZH Time zone hour X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZH') FROM dual;

SYSTIMESTAMP                         TO_
-----------------------------------  ---
24-MAR-20 04.18.32.364000 PM -05:00  -05
TZM Time zone minute X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZM') FROM dual;

SYSTIMESTAMP                         TO
-----------------------------------  --
24-MAR-20 04.18.10.760000 PM -05:00  00
TZR Time zone region X  
SELECT systimestamp, TO_CHAR(SYSTIMESTAMP, 'TZR') FROM dual;

SYSTIMESTAMP                         TO_CHAR(SYSTIMESTAMP,'TZR')
-----------------------------------  ------------------------
24-MAR-20 04.18.54.609000 PM -05:00  -05:00

SELECT TO_CHAR( TO_TIMESTAMP_TZ('2020-02-15 13:27:58 CET', 'YYYY-MM-DD HH24:MI:SS TZR'), 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
FROM dual;

TO_CHAR(TO_TIMESTAMP_TZ('2
--------------------------
2020-02-15 13:27:58 +01:00
 
TO_TIMESTAMP_TZ
Converts a string to an Timestamp with Timezone Data Type
Overload 1		 TO_TIMESTAMP(right IN VARCHAR2 CHARACTER SET ANY_CS) RETURN TIMESTAMP WITH TIME ZONE;
TBD
Overload 2 TO_TIMESTAMP(
left   IN VARCHAR2 CHARACTER SET ANY_CS,
format IN VARCHAR2 CHARACTER SET LEFT%CHARSET,
parms  IN VARCHAR2 CHARACTER SET LEFT%CHARSET)
RETURN TIMESTAMP WITH TIME ZONE;
TBD
Overload 3 TO_TIMESTAMP(left IN VARCHAR2, format IN VARCHAR2) RETURN TIMESTAMP WITH TIME ZONE;
SELECT TO_TIMESTAMP_TZ('2020-01-07 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM dual;

TO_TIMESTAMP_TZ('2020-01-0711:00:00-8:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
----------------------------------------------------------------------
07-JAN-20 11.00.00.000000000 AM -08:00

SELECT TO_TIMESTAMP_TZ('2020-04-19T11:50:19-04:00','YYYY-MM-DD"T"HH24:MI:SS-TZH:TZM')
FROM dual;

TO_TIMESTAMP_TZ('2020-04-19T11:50:19-04:00','YYYY-MM-DD"T"HH24:MI:SS-TZH:TZ
---------------------------------------------------------------------------
19-APR-20 11.50.19.000000000 AM +04:00
Overload 4 TO_TIMESTAMP(right IN TIMESTAMP) RETURN TIMESTAMP WITH TIME ZONE;
TBD
Overload 5 TO_TIMESTAMP(arg IN DATE) RETURN TIMESTAMP WITH TIME ZONE;
TBD
Overload 6 TO_TIMESTAMP(arg IN TIMESTAMP WITH LOCAL TIME ZONE) RETURN TIMESTAMP WITH TIME ZONE;
TBD
 
TO_UTC_TIMESTAMP_TZ
Accepts an ISO 8601 date format string as VARCHAR2 and returns an instance of SQL data type TIMESTAMP WITH TIMEZONE to_utc_timestamp_tz(<arg> IN VARCHAR2 DEFAULT)
RETURN TIMESTAMP WITH TIMEZONE;
col systimestamp format a36

SELECT systimestamp, to_utc_timestamp_tz('2020-03-15')
FROM dual;

SYSTIMESTAMP                         TO_UTC_TIMESTAMP_TZ('2020-03-15')
------------------------------------ -------------------------------------
24-MAR-20 04.21.53.614000 PM -05:00  15-MAR-20 12.00.00.000000000 AM +00:00
 
TO_YMINTERVAL
Converts a character string to an INTERVAL YEAR TO MONTH type TO_YMINTERVAL(right IN VARCHAR2) RETURN INTERVAL YEAR TO MONTH;
SELECT hire_date, hire_date+TO_YMINTERVAL('01-02') "14 months" FROM emp;
 
TRANSLATE USING
Converts char into the character set specified for conversions between the database character set and the national character set TRANSLATE(char_string USING <CHAR_CS character_set | NCHAR_CS character set>);
conn oe/oe@pdbdev

CREATE TABLE translate_tab (
char_col  VARCHAR2(100),
nchar_col NVARCHAR2(50));

desc translate_tab

INSERT INTO translate_tab
SELECT NULL, translated_name
FROM product_descriptions
WHERE product_id = 3501;

col char_col format a30
col nchar_col format a30

SELECT * FROM translate_tab;

UPDATE translate_tab
SET char_col = TRANSLATE(nchar_col USING CHAR_CS);

SELECT * FROM translate_tab;
 
TREAT
Changes the declared type of an expression TREAT(<expression> AS REF schema.type)) RETURN VARCHAR2;
SELECT name, TREAT(VALUE(p) AS employee_t).salary SALARY
FROM persons p;
 
UNISTR
Convert String To The National Character Set (either UTF8 or UTF16) UNISTR(ch IN VARCHAR2 CHARACTER SET ANY_CS) RETURN NVARCHAR2;
SELECT UNISTR(ASCIISTR(CHR(128) || 'Hello' || CHR(255)))
FROM dual;
 
VALIDATE_CONVERSION
Determines whether an expression can be converted to the specified data type. If TRUE returns 1 else 0 VALIDATE_CONVERSION(<expression> AS <expression_type> [format [, <'nls_parameter>']]);
SELECT validate_conversion('$42.95' AS BINARY_FLOAT)
FROM dual;

SELECT validate_conversion('$42.95' AS BINARY_FLOAT, '$99D99')
FROM dual;

SELECT validate_conversion(1000 AS BINARY_DOUBLE)
FROM dual;

SELECT validate_conversion('1234.56' AS BINARY_FLOAT)
FROM dual;

SELECT validate_conversion('July 04, 1776, 12:00' AS DATE, 'Month dd, YYYY, HH24:MI', 'NLS_DATE_LANGUAGE = American')
FROM dual;

SELECT validate_conversion('200 00:00:00' AS INTERVAL DAY TO SECOND)
FROM dual;

SELECT validate_conversion('P1Y2M' AS INTERVAL YEAR TO MONTH)
FROM dual;

SELECT validate_conversion('$100,00' AS NUMBER, '$999D99', 'NLS_NUMERIC_CHARACTERS = '',.''')
FROM dual;

SELECT validate_conversion('15-Mar-18 18:18:18' AS TIMESTAMP, 'DD-MON-YY HH24:MI:SS')
FROM dual;

SELECT validate_conversion('2018-03-15 12:12:12 -6:00' AS TIMESTAMP WITH TIME ZONE, 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM dual;

SELECT validate_conversion('15-Mar-17 11:42:00' AS TIMESTAMP WITH LOCAL TIME ZONE, 'DD-MON-YY HH24:MI:SS')
FROM dual;

Related Topics
Analytic Functions
Built-in Functions
CASE
CAST
Character Set Functions
Collection Functions
Data Mining Functions
Date Functions
Miscellaneous Functions
Numeric Functions
OLAP Functions
Operators (Built-in)
Regular Expressions
REPLACE
String Functions
SUBSTRING
Timestamp Functions
TO_ACLID
TRANSLATE
Undocumented Oracle
UTL_RAW
XML Functions
What's New In 21c
What's New In 23c