Conversion functions

 

Conversion function

1.Number functions note: - no of 9’s should be equal to given number

Ø  Select to_char (1234,’9999D99’) from dual; it results as 1234.00

Ø  Select to_char (1234,’9.9EEEE’) from dual; it results as 1.2E+03, means scientific notation form of 1234

Ø  Select to_char (1234,’9G999’) from dual; it results 1,234

Ø  Select to_char (1234,’L9999’) from dual; it results $1234, means local currency

Ø  Select to_char (-1234,’9999MI’) from dual; it results 1234-, displays ‘– ‘symbol last position

Ø  Select to_char (-1234,’9999PR’) from dual; it results <1234>, displays -ve no between ‘<>’

Ø  Select to_char (10,’RN’), to_char (10,’rn’) from dual; it results X, x , means roman indicator of given number

Ø  Select to_char (10,’S99’), to_char(-10,’S99’), to_char (10,’99S’), to_char(-10,’99S’) from dual; it results +10, -10, 10+, 10- , means sign indicator

Ø  Select to_char (10,’XX’) from dual; it results A, means hexa decimal value for that given number

Ø  Select to_char (105,’0999’) from dual; it results 0105

Ø  Select to_char (100-10,’999’), to_char (100-110,’999’) from dual; it results 90, -10

Ø  Select to_char (10,’C99’) from dual; it results USD10, means ISO currency indicator

 

 

2.Date functions

Ø  Select to_char (sysdate, ‘AD’) from dual; it displays AD, means AD or BC indicator

Ø  Select to_char (sysdate, ‘AM’) from dual; it displays PM, means AM or PM indicator

Ø  Select to_char (sysdate, ‘CC’) from dual; it displays 21, means century indicator

Ø  Select to_char (sysdate, ‘D’) from dual; it displays numeric week day indicator starting from sun like 1

Ø  Select to_char (sysdate, ‘DAY’) from dual; it results full spelling of a day like SUNDAY

Ø  Select to_char (sysdate, ‘DD’) from dual; it results date part of given date

Ø  Select to_char (sysdate, ‘DDD’) from dual; it results no of days counting from jan 1^st

Ø  Select to_char (sysdate, ‘DY’) from dual; it results abbreviated week day like SUN

Ø  Select to_char (sysdate, ‘IW’) from dual; it results no of weeks counting from jan 1^st

Ø  Select to_char (sysdate, ‘YYYY’) from dual; it results year part from given date like 2021

Ø  Select to_char (sysdate, ‘YEAR’) from dual; it results full spelling of the year like twenty twenty-one

Ø  Select to_char (sysdate, ‘W’) from dual; it results week no of the given date like 1

Ø  Select to_char (sysdate, ‘WW’) from dual; it results week no from jan 1^st like 12

Note: - ‘WW’ displays normal week day from calendar, ‘IW’ displays ISO standard week day

Ø  Select to_char (sysdate, ‘Q’) from dual; it results ISO standard quarter no

Ø  Select to_char (sysdate, ‘J’) from dual; it calculates no days from jan 14^th 712 BC

Ø  Select to_char (sysdate, ‘MM’) from dual; it results numeric month like 6

Ø  Select to_char (sysdate, ‘MON’) from dual; it results abbreviated month like JUN

Ø  Select to_char (sysdate, ‘HH12’), to_char(sysdate,’HH24’) from dual; it results 9, 21, means hour no

Ø  Select to_char (sysdate, ‘MI’) from dual; it results minutes indicator

Ø  Select to_char (sysdate, ‘SS’) from dual; it results seconds indicator

Ø  Select to_char (sysdate, ‘RM’) from dual; it results roman month indicator like VI

Ø  Select to_char (sysdate, ‘DDTH-MON-YEAR’) from dual; it results 5^th-JUN   -TWENTY TWENTY-ONE

Ø  Select to_char (sysdate, ‘DDTHSP-MON-YEAR’) from dual; it results FIFTH-JUN   -TWENTY TWENTY-ONE

Ø  Select to_char (sysdate, ‘DDTHSPFM-MON-YEAR’) from dual; it results 5^th-JUN-TWENTY TWENTY-ONE, means it removes blank spaces

Ø  Select to_char (sysdate, ‘JSP’) from dual; it results spelled Julien day

        Please watch and subscribe my Channel

        Kamal SQL CLASSES in Telugu