Oracle to_char, to_number, and to_date usage

Keywords: Oracle to_char, to_number, and to_date usage

TO_CHAR converts a date or number to a string.
TO_DATE converts a string to a date type conversion function in the database.
TO_NUMBER converts a character to a number

TO_CHAR
Use the TO_CHAR function to process numbers
TO_CHAR (number, 'format ')
TO_CHAR (salary, '$99,999.99 ');
Use the TO_CHAR function to process a date
TO_CHAR (date, 'format ');

TO_NUMBER
Use the TO_NUMBER function to convert characters to numbers.
TO_NUMBER (char [, 'format'])

TO_DATE
Use the TO_DATE function to convert a character to a date
TO_DATE (char [, 'format'])

Digit format
9 represents a number
0 force display 0
$ Place a $ character
L place a floating local currency character
. Display decimal point
, Display the thousands of indicators

Date Format
Format control description
YYYY, YYY, and YY represent four, three, and two digit years respectively.
YEAR spelling
MM digital month
Full MONTH
The abbreviation of MON month
DD digital day
DAY and week
Abbreviation of DY week
AM indicates the morning or afternoon
HH24, HH12, or 24-hour
MI minutes
SS seconds
SP number spelling
Ordinal word of TH number

"Special Character" is a special character
HH24: MI: ss am 15:43:20

Date example:
SELECT TO_DATE ('2017-05-01 19:25:34 ', 'yyyy-MM-DD HH24: MI: ss') FROM DUAL
SELECT TO_DATE ('2017-05-01 ', 'yyyy-MM-DD HH24: MI') FROM DUAL
SELECT TO_DATE ('2017-05-01 19', 'yyyy-MM-DD hh24') FROM DUAL
SELECT TO_DATE ('2017-05-01 ', 'yyyy-MM-DD') FROM DUAL
SELECT TO_DATE ('2017-05 ', 'yyyy-mm') FROM DUAL
SELECT TO_DATE ('20140901', 'yyyy') FROM DUAL

Date description:
When the input parameters corresponding to HH, MI, and SS are omitted, Oracle uses 0 as the DEFAULT value. If the input date data ignores the time part, Oracle sets the hour, minute, and second part to 0, that is, it will take the whole day.

Similarly, if the DD parameter is ignored, Oracle uses 1 as the default value of the day, that is, it takes the entire month.

However, do not be confused by this kind of "inertia". If the MM parameter is ignored, Oracle will not take the entire year and the entire month.

Note:
1. when Oracle's to_date function is used for date conversion, the format of "yyyy-MM-dd HH: mm: ss" may be intuitively used for conversion, however, an error occurs in Oracle: "ORA 01810 format code appears twice ". For example, select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mm: ss') from dual, because SQL statements are case insensitive, MM and mm are considered to be the same format code, so Oracle SQL uses mi instead of minutes. Select to_date ('1970-01-01 13:14:20 ', 'yyyy-MM-dd HH24: mi: ss') from dual;
2. It will be displayed in the form of 24 hours with HH24
Select to_char (sysdate, 'yyyy-MM-dd HH24: mi: ss') from dual; // mi is minute
Select to_char (sysdate, 'yyyy-MM-dd HH24: mm: ss') from dual; // mm displays the month

For more information about Oracle, see Oracle topics page http://www.bkjia.com/topicnews.aspx? Tid = 12