[pl / SQL] conversion function (date / string / number)

Time:2021-9-15

After reading this chapter, you will learn the following

  • What kinds of conversion functions are there? Which one is usually used most?
  • Why use conversion functions?
  • What are the applicable conditions for their respective conversion relationships?

There are three types of data: number, character and date
Corresponding to the three types of functions used to convert them: conversion function!

To_ Date to date
Date can only be converted when there is’ – ‘in it. Only errors will occur in numbers, such as:

[pl / SQL] conversion function (date / string / number)

SELECT DATE'2019-01-01' FROM DUAL;  ---  Cast to date format
  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 has only the above two forms
  

   Note: the year, month and day in characters or numbers must be within the date range
   SELECT TO_ Date ('2019-13-08 ',' yyyy-mm-dd '), - wrong month
          TO_ Date (20190732, 'yyyy-mm-dd') -- wrong number of days
     FROM DUAL;
   --There are - or / characters in the date. The date type must be converted to - or/ 
   SELECT TO_ Date ('2019-07-08 ',' yyyymmdd '), - error
          TO_ DATE(20190708,'YYYY-MM-DD')  FROM DUAL;   ---  success

[pl / SQL] conversion function (date / string / number)

[pl / SQL] conversion function (date / string / number)

[pl / SQL] conversion function (date / string / number)

To_ Char() to 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 (date '2019-08-30', 'WW'), - '35' week 35
       TO_ Char (date '2019-08-30', 'IW'), - '35' week 35 (NATURAL week)
       TO_ Char (date '2019-08-30', 'Q'), - '3' Q3
       TO_CHAR(DATE'2019-08-30','MM'),     -- '08'
       TO_CHAR(DATE'2019-08-30','DD'),     -- '30'
       TO_ Char (date '2019-08-30','d ') -- the day of the week in' 6 '(Sunday is the first day)
    FROM DUAL;

[pl / SQL] conversion function (date / string / number)

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 (date '2019-08-30', 'WW'), - '35' week 35
       TO_ Char (date '2019-08-30', 'IW'), - '35' week 35 (NATURAL week)
       TO_ Char (date '2019-08-30', 'Q'), - '3' Q3
       TO_CHAR(DATE'2019-08-30','MM'),     -- '08'
       TO_CHAR(DATE'2019-08-30','DD'),     -- '30'
       TO_ Char (date '2019-08-30','d ') -- the day of the week in' 6 '(Sunday is the first day)
    FROM DUAL;

PS: If the date is converted to a number: it is converted to a character, then forwarded to a number, and finally displayed on the right.
[pl / SQL] conversion function (date / string / number)