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
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, 'FM Day, 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, 'FX Day, 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:SSX FF') 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, 'FM HH:MI AM') B,
TO_CHAR (dt, 'FM HHFM :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;