To_date is converting a string to a date type conversion function in a database
To_char is to convert a date or number to a string
To_number converting characters to numbers
To_date
Convert a character to a date using the To_date function
To_date (char, ' format ')
Example 1:
Select To_date (' 2012-07-01 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') from dual;
Select To_date (' 2012-07-01 10:00am ', ' yyyy-mm-dd Hh24:mi ') from dual;
Select To_date (' 2012-07-01 ', ' yyyy-mm-dd hh24 ') from dual;
Select To_date (' 2012-07-01 ', ' YYYY-MM-DD ') from dual;
Select To_date (' 2012-07 ', ' yyyy-mm ') from dual;
Select To_date (' + ', ' yyyy ') from dual;
--yyyy/yyy/yy represents 4-bit /3-bit /2-bit digital years, MM for month, DD for day, and Hh24/hh12 for 24-hour /12-hour systems respectively . MI stands for minutes, SS stands for seconds
Example 2:
The CZSJ in table Jmzs is the VACHAR2 type, converted to the date type:
Select To_date (CZSJ, ' Yyyy-mm-dd hh24:mi:ss ') from Jmzs;
To_char
Working with numbers using the To_char function
To_char (number, ' format ')
To_char (Salary, ' $99,999.99 ');
Working with dates using the To_char function
To_char (date, ' format ');
Example 1:
Select To_char (to_date (' 2012-07-01 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Yyyymmddhhmiss ') from dual;
Example 2:
The CZSJ in table Jmzs is the VACHAR2 type, first converted to the date type, then to the char type:
Select To_char (to_date (CZSJ, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyymmddhhmiss ') from Jmzs;
To_number
Convert characters to numbers using the To_number function
To_number (char, ' format ')
Example 1:
Select To_number (To_char (to_date (' 2012-07-01 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss '), ' Yyyymmddhhmiss ')) from dual;
Example 2:
The CZSJ in the table Jmzs isvachar2 type, converted to date type, then to char type, and finally to number type:
Select To_number (To_char(to_date(CZSJ, ' yyyy-mm-dd hh24:mi:ss '), ' Yyyymmddhhmiss ')) from Jmzs;
Oracle's To_number, TO_CHAR, to_date usage