Oracle conversion Functions To_char, to_date, To_number

To_char, To_date, To_number

To_char converts a date or number to a string
To_date is converting a string to a date type conversion function in a database
To_number converts a character to a number

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 ');

To_number ²
Convert characters to numbers using the To_number function
To_number (char[, ' format '])

to_date ²
Convert a character to a date using the To_date function
To_date (char[, ' format '])

Number format Format ²
9 represents a number
0 Force Show 0
$ place a $ character
L Place a floating local currency character
. Show decimal point
, display the thousand indicator

Date Format ²
Format Control Description
YYYY, YYY, YY represent 4-bit, 3-bit, 2-digit years respectively
Year's spelling
MM Digital Month
MONTH full spell of the month
The abbreviation of MON month
DD Digital Day
Day week's full spell
DY Week's abbreviation
Am means morning or afternoon
HH24, HH12 12-hour or 24-hour system
MI min
SS seconds
The spelling of an SP number
Ordinal number of TH digit

"Special characters" if special characters
HH24:MI:SS AM 15:43:20 PM

Date Example:
SELECT to_date (' 2006-05-01 19:25:34 ', ' yyyy-mm-dd HH24:MI:SS ') from DUAL
SELECT to_date (' 2006-05-01 19:25 ', ' yyyy-mm-dd hh24:mi ') from DUAL
SELECT to_date (' 2006-05-01 ', ' yyyy-mm-dd HH24 ') from DUAL
SELECT to_date (' 2006-05-01 ', ' yyyy-mm-dd ') from DUAL
SELECT to_date (' 2006-05 ', ' yyyy-mm ') from DUAL
SELECT to_date (' 2006 ', ' YYYY ') from DUAL

Date Description:
When omitting the input parameters for HH, MI, and SS, Oracle uses 0 as the default value. If the date data that you enter ignores the time part, Oracle will set the time, minute, and second portions to 0, which means that it will take the whole day.

Similarly, by ignoring the DD parameter, Oracle takes 1 as the default value for the day, which means the whole month is taken.

However, do not be fooled by this "inertia", if the mm parameter is ignored, Oracle will not take the whole year, rounding to the current month.

Attention:
1. When using Oracle's To_date function for date conversion, it is possible to intuitively convert the format of "Yyyy-mm-dd HH:mm:ss" into a format, but it can cause errors in Oracle: "ORA 01810-format code appears two times." For example: Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mm:ss ') from dual because SQL is case-insensitive, mm and mm are considered the same format code, So Oracle's SQL uses MI instead of minutes. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
2. Another 24 hours to display the form to use 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