CONVERSION FUNCTIONS
Implicit character to date conversion are possible when the character string conforms to the following date pattern.
[D/DD] SEPERATOR [MON/MONTH] SEPERATOR [R/RR/YY/YYYY].
All arithmetic functions containing a NULL value passed in as input will return number values only.
Sub query may return NULL values but won't retrieve NULL records from the table.
[D/DD] SEPERATOR [MON/MONTH] SEPERATOR [R/RR/YY/YYYY].
All arithmetic functions containing a NULL value passed in as input will return number values only.
Sub query may return NULL values but won't retrieve NULL records from the table.
- TO_CHAR;
- TO_DATE;
- TO_NUMBER;
- TO_TIMESTAMP;
- TO_TIMESTAMP_TZ;
- TO_YMINTERVAL;
- TO_DSINTERVAL;
- TO_BINARY_FLOAT;
- TO_BINARY_DOUBLE;
- TO_BLOB;
- TO_CLOB;
- TO_LOB
- BIN_TO_NUM;
- NUMTOYMINTERVAL;
- NUMTODSINTERVAL;
- RAWTOHEX
- TIMESTAMP_TO_SCN
- SCN_TO_TIMESTAMP;
- TO_NCHAR;
- TO_NCLOB;
SYN: TO_CHAR (DATE,’FORMAT’);
$
9 ------------ DIGIT REPRESENTATION
0
. ----------- SPECIFIES THE DECIMAL
,
PR ----------- ENCLOSE THE –VE VALUES IN ANGLE BRACKETS
MI ----------- REPRESENTS THE –VE SIGN
S ----------- SIGN
L ----------- LOCAL CURRENT SYMBOL
B ----------- BLANK SPACE
C ----------- CURRENCY CODE
D ----------- DECIMAL POINT
EEEE ------- SPECIFIES THE EXPONENTIAL
G ----------- GROUPING
U
V
X
N
EG: SELECT TO_CHAR(SYSDATE,’D’) FROM DUAL;
SELECT TO_CHAR(SYSDATE,’DD’)
FROM DUAL;
SELECT TO_CHAR(SYSDATE,’DY’)
FROM DUAL;
SELECT TO_CHAR(SYSDATE,’CC’)
FROM DUAL;
SELECT TO_CHAR(SYSDATE,’FM
MM/DD/YY’) FROM DUAL;
SELECT * FROM EMP WHERE
TO_CHAR(HIREDATE,’MON’)=’FEB’;
SELECT * FROM EMP WHEE
TO_CHAR(HIREDATE,’MM’)=02;
SELECT * FROM EMP WHERE
TO_CHAR(HIREDATE,’YY’)=81;
SELECT * FROM EMP WHERE
TO_CHAR(HIREDATE,’D’)=1;
SELECT * FROM EMP WHERE
TO_CHAR(HIREDATE,’W’)=1;
TO_DATE: Converts character format to date
format
SYN: TO_DATE
(‘C’,’FORMAT’);
EG: SELECT TO_DATE (‘10’,’DD’) FROM DUAL;
SELECT TO_DATE (‘10’,’MM’)
FROM DUAL;
SELECT TO_DATE (‘10’,’YY’)
FROM DUAL;
SELECT TO_DATE (‘FEB’,’MON’)
FROM DUAL;
SELECT TO_CHAR
(ROUND(TO_DATE(‘11’,’YY’),’YEAR’),’MM’) FROM DUAL;
------------------------------------------------------------------------------------
TO_NUMBER:
Converts character into NUMBER FORMAT
TO_YMINTERVAL:
Converts the character into YEAR AND MONTH INTERVAL
TO_DISINTERVAL:
Converts the character into DAY,HOUR,MINUTE AND SECONDS INTERVAL
BIN_TO_NUM:
BINARY TO NUMBER
NUMTOYMINTERVAL:
NUMBER TO YEAR AND MONTH INTERVALS
NUMTODSINTERVAL:
NUMBER TO DAY ,HOUR,MINUTE AND SECONDS INTERVAL
EG:
SELECT
TO_TIMESTAMP(‘11’,’FF’) FROM DUAL;
SELECT TO_TIMESTAMP(‘11’,’HH’) FROM
DUAL;
SELECT TO_TIMESTAMP_TZ(‘05’,’TZH’) FROM
DUAL;
SELECT TO_YMINTERVAL (’10-06’) FROM
DUAL;
SELECT
SYSDATE,SYSDATE+TO_YMINTERVAL(’10-06’) FROM DUAL;
SELECT SYSTIMETAMP,
SYSTIMESTAMP+TO_DSINTERVAL (’10 10:10:10’)
FROM DUAL;
SELECT BIN_TO_NUM(1,1,1) FROM DUAL;
SELECT NUMTOYMINTERVAL(11,’YEAR’) FROM
DUAL;
SELECT
SYSDATE,SYSDATE+NUMTOYMINTERVAL(11,’YEAR’) FROM DUAL;
SELECT NUMTOYMINTERVAL(11,’MONTH’)
FROM DUAL;
SELECT NUMTOYMINTERVAL(9999,’YEAR’)
FROM DUAL;
SELECT NUMTODSIINTERVAL(10,’DAY’) FROM
DUAL;
SELECT NUMTODSINTERVAL(10,’HOUR’) FROM
DUAL;
SELECT NUMTODSINTERVAL(10,’SECONDS’)
FROM DUAL;
SELECT ORA_ROWSCN FROM DUAL;
SELECT SCN_TO_TIMESTAMP(ORA-ROWSCN)
FROM DUAL;
SELECT
TIMESTAMP_TO_SCN (SCN_TO_TIMESTAMP(ORA_ROWSCN)) FROM DUAL;
SELECT
TO_NUMBER (‘99’) FROM DUAL;
SELECT SYSDATE+TO_NUMBER(‘10’) FROM DUAL;
--------------------------------------------------------------------------
NUMBER FORMATS:
EG: SELECT
SAL,TO_CHAR(SAL,’$9999’) FROM EMP;
SELECT SAL,TO_CHAR(SAL,’$9999.99’)
FROM EMP;
SELECT SAL,TO_CHAR(SAL,’$9999D99’)
FROM EMP;
SELECT TO_CHAR(-10,’99PR’) FROM DUAL;
SELECT TO_CHAR(-10,’99MI’) FROM DUAL;
SELECT TO_CHAR(10,’99U9’) FROM DUAL;
SELECT TO_CHAR(10,’L99’) FROM DUAL;
SELECT TO_CHAR(10,’C99’) FROM DUAL;