CONVERSION FUNCTIONS

CAST - function converts one datatype to another.

SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;
SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) FROM dual;

SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING FROM dual;


from_tz function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

select from_tz(TIMESTAMP '2005-09-11 01:50:42', '5:00') from dual --11-Sep-05 01.50.42.000000 AM +05:00
select from_tz(TIMESTAMP '2005-09-11 01:50:42', '-7:00') from dual --11-Sep-05 01.50.42.000000 AM -07:00


hextoraw function converts a hexadecimal value into a raw value.

select hextoraw('45D') from dual -- would return '045D' as a raw value
select hextoraw('7E') from dual -- would return '7E' as a raw value
select hextoraw('9FA') from dual -- would return '7E' as a raw value

rawtohex
SELECT rawtohex('AB') FROM DUAL -- would return '4142' if run as an SQL function and 'AB' if run as a PLSQL function.
SELECT rawtohex('7E') FROM DUAL -- would return '3745' if run as an SQL function and '7E' as a PLSQL function.

to_char

SELECT to_char(sysdate, 'yyyy/mm/dd') FROM DUAL -- would return '2003/07/09'
SELECT to_char(sysdate, 'Month DD, YYYY') FROM DUAL -- would return 'July 09, 2003'
SELECT to_char(sysdate, 'FMMonth DD, YYYY') FROM DUAL -- would return 'July 9, 2003'
SELECT to_char(sysdate, 'MON DDth, YYYY') FROM DUAL -- would return 'JUL 09TH, 2003'
SELECT to_char(sysdate, 'FMMON DDth, YYYY') FROM DUAL -- would return 'JUL 9TH, 2003'
SELECT to_char(sysdate, 'FMMon ddth, YYYY') FROM DUAL -- would return 'Jul 9th, 2003'

to_date

SELECT to_date('2003/07/09', 'yyyy/mm/dd') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('070903', 'MMDDYY') FROM DUAL -- would return a date value of July 9, 2003.
SELECT to_date('20020315', 'yyyymmdd') FROM DUAL -- would return a date value of Mar 15, 2002.

LOB functions
select to_clob(nclob_column) from suppliers;
select to_nclob(lob_column) from suppliers;
select to_lob(long_column) from suppliers;

to_number function converts a string to a number.

select to_number('1210.76', '99999.99') from dual -- would return the number 1210.73
select to_number('546', '999') from dual -- would return the number 546
select to_number('23', '99') from dual -- would return the number 23

to_timestamp
select to_timestamp('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

select to_timestamp('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.


to_timestamp_tz

select to_timestamp_tz('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM') from dual
-- would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

select to_timestamp_tz('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM') from dual
-- would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

to_yminterval

select sysdate+to_yminterval('03-11') from dual -- would return 3 years 11 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('01-05') from dual -- would return 1 year 5 months as an INTERVAL YEAR TO MONTH type
select sysdate+to_yminterval('00-01') from dual -- would return 0 years 1 month as an INTERVAL YEAR TO MONTH type