By the end of this chapter you will have learned the following
- What kinds of conversion functions are there, and which one is usually used the most?
- Why use a conversion function?
- What are the applicable conditions for the relationship between the respective transformations?
There are three types of data types: Numbers, Characters, and Dates, which correspond to the three types of functions used to convert them: Convert Functions!
To_Date (‘ Date ‘, ‘Date’, ‘To_Date’, ‘To_Date’)
SELECT DATE'2019-01-01' FROM DUAL; SELECT TO_DATE('2019-01-01','YYYY/MM/DD') FROM DUAL; SELECT TO_DATE('2019-01-01','YYYY-MM-DD') FROM DUAL; SELECT TO_DATE('2019-01-01','YYYYMMDD') FROM DUAL; ---- error, date conversion only has the above two forms note: SELECT TO_DATE('2019-13-08',' yyyy-mm-dd '), TO_DATE('2019-13-08',' yyyy-mm-dd '), TO_DATE(20190732,' yyyy-mm-dd ') FROM DUAL; SELECT TO_DATE('2019-07-08','YYYYMMDD'), TO_DATE(20190708,' yyyy-mm-dd ') FROM DUAL; -- -- -- success
To_Char() to a character
SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL; SELECT TO_CHAR(DATE'2019-08-30','YYYY'), -- '2019' TO_CHAR(DATE'2019-08-30','YYYYMM'), - '201908' TO_CHAR (the DATE '2019-08-30', 'WW), -' 35 '35 weeks TO_CHAR (the DATE' 2019-08-30 ', 'IW'), - '35' 35 weeks (natural weeks) TO_CHAR (the DATE '2019-08-30', 'Q'), - '3' third quarter TO_CHAR (the DATE '2019-08-30', 'MM), - '08' TO_CHAR (the DATE '2019-08-30', 'DD'), - '30' TO_CHAR (the DATE '2019-08-30', 'D'), '6' what day of the week (Sunday is the first day) FROM DUAL;
To_Number() is converted to a number
SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL; SELECT TO_CHAR(DATE'2019-08-30','YYYY'), -- '2019' TO_CHAR(DATE'2019-08-30','YYYYMM'), - '201908' TO_CHAR (the DATE '2019-08-30', 'WW), -' 35 '35 weeks TO_CHAR (the DATE' 2019-08-30 ', 'IW'), - '35' 35 weeks (natural weeks) TO_CHAR (the DATE '2019-08-30', 'Q'), - '3' third quarter TO_CHAR (the DATE '2019-08-30', 'MM), - '08' TO_CHAR (the DATE '2019-08-30', 'DD'), - '30' TO_CHAR (the DATE '2019-08-30', 'D'), '6' what day of the week (Sunday is the first day) FROM DUAL;
PS:Date to number: first to characters, then forward to numbers, and finally to the right display.