SQL Conversion Functions TO_CHAR, TO_DATE, TO_NUMBER

--====================================================

Conversion Functions are

1. TO_NUMBER

2. TO_CHAR

3. TO_DATE

--====================================================

--Conversion                                CHAR   to   DATE

--====================================================

ORACLE DATE FORMAT fix :- at the time of installation ( 'DD-MON-RRRR')

DROP TABLE XXSD_CONVERSION_TL 

CREATE TABLE XXSD_CONVERSION_TL

( 

COL_DATE DATE,

COL_NUMBER NUMBER,

COL_STRING VARCHAR2(100)

)

EX_1:-

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ('13-APR-2020')

EX_2:-

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ('APR-13-2020')

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES (TO_DATE('APR-13-2020', 'MON-DD-RRRR' ) )

EX_3:-

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ('2020-APR-13')

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES (TO_DATE('2020-APR-13', 'YYYY/MON%DD' ) )

EX_4:-

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ('2020-13-APR')

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ( TO_DATE('2020-13-APR', 'YYYY-DD-MON' ) )

EX_5:-

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES ('2020APR13')

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_DATE ) VALUES (TO_DATE('2020APR13', 'RRRRMONDD' ) )

select * from XXSD_CONVERSION_TL

Result :-

COL_DATE	COL_NUMBER	COL_STRING
13-Apr-20
13-Apr-20
13-Apr-20
13-Apr-20
13-Apr-20

--- Conversion ----

select  TO_DATE('APRIL132020', 'MONTHDDYYYY' ) FROm DUAL

Result :- 13-Apr-2020

select  TO_DATE('123123', 'DD-MON-YYYY' ) FROm DUAL

Result :- ERROR

--====================================================

--Conversion                                CHAR   to   NUMBER

--====================================================

Oracle Number Format :- 123456789.00

EX_1:-

INSERT INTO XXSD_CONVERSION_TL ( COL_NUMBER ) VALUES (123423.90)

EX_2:-

INSERT INTO XXSD_CONVERSION_TL ( COL_NUMBER ) VALUES ('$1,2345.00')

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_NUMBER ) VALUES ( TO_NUMBER('$1,2345.00', '$9,999,9999.00' ) )

EX_3:-

INSERT INTO XXSD_CONVERSION_TL ( COL_NUMBER ) VALUES (1,2345.00)

--- Conversion ----

INSERT INTO XXSD_CONVERSION_TL ( COL_NUMBER ) VALUES ( TO_NUMBER('1,2345.00', '9,999,9999.00' )  )

--=======================

select * from XXSD_CONVERSION_TL

COL_DATE	COL_NUMBER	COL_STRING
13-Apr-20
13-Apr-20
13-Apr-20
13-Apr-20
13-Apr-20
	12345.00
	12345
	12345

--- COnversion ----

select  TO_CHAR(12345, '000000000' ) FROM DUAL

Result :- 000012345

select  TO_NUMBER('$1,2345.00', '$9,999,9999.00' ) FROM DUAL

Result :- 12345

select  TO_NUMBER('1,2345.00', '9,999,9999.00' ) FROM DUAL

Result :- 12345

select  TO_NUMBER(' 0012345.00', '0000000.00' ) FROM DUAL

Result :- 12345

--====================================================

--Conversion                               

NUMBER   to   CHAR  - OK

NUMBER   to DATE   not Possible

--====================================================

select TO_CHAR(12345, '000000000' )

, TO_CHAR(12345, '0000000.00' )AA

, TO_CHAR(12345, '9,999,9999.00' )AA

, TO_CHAR(12345, '$9,999,9999.00' )AA

from DUAL

Result :-

TO_CHAR(12345,'000000000')	AA	AA_1	AA_2
18-Oct-33	12345	12,345.00	$1,2345.00

--====================================================

--Conversion                               

DATE   to   CHAR  - OK

DATE   to   NUMBER - Not Possible

--====================================================

SELECT sysdate, TO_CHAR(sysdate,'MONTH/YYYY')DATE1 FROM DUAL

Result :- ‘APRIL    /2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDth MON YYYY' ) DATE1  FROM DUAL

Result :- ‘13TH APR 2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'ddth MON YYYY' ) DATE1  FROM DUAL

Result :- ‘13th APR 2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'ddSPTH MON YYYY' ) DATE1  FROM DUAL

Result :-‘ thirteenth APR 2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDSPTH MON YYYY' ) DATE1  FROM DUAL

Result :- ‘THIRTEENTH APR 2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DDth "Of" Month YYYY' ) DATE1  FROM DUAL

Result :- ‘13TH Of April     2020’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD-MON-YYYY hh:mi:ss am' ) DATE1  FROM DUAL

Result :-‘ 13-APR-2020 08:04:41 pm’

SELECT SYSDATE, TO_CHAR(SYSDATE, 'MON' ) DATE1,TO_CHAR(SYSDATE, 'MONTH' ) DATE1  FROM DUAL  

Result :- ‘APRIL‘

SELECT SYSDATE, TO_CHAR(SYSDATE, 'DD' ) DATE1,TO_CHAR(SYSDATE, 'dd' ) DATE1  FROM DUAL

Result :- 13

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY' ) DATE1,TO_CHAR(SYSDATE, 'RRRR' ) DATE1  FROM DUAL

Result :- 2020

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY hh24:mi:ss am' ) DATE1   FROM DUAL

Result :- ‘13-APR-2020 20:05:23 pm’

--====================================================