The Oracle TO_CHAR function is used to convert a DATE value to string or text value.
The TO_CHAR function is very useful for formatting the dates in a table to a specified date format.
Syntax
The syntax of TO_CHAR function is as follows:
TO_CHAR( date, format )
Here,
Date – It is a DATE value in a table. The data type of this value can be DATE, INTERVAL, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.
Format – It is the date format in which we want our string to be. When we convert a date value to string, we can convert into different formats as per our requirement.
The TO_CHAR function returns the string in a desired date format like DD-MM-YYYY or MMM-DD-YYYY etc.
Examples
1. TO_CHAR with Numbers and Decimals
TO_CHAR function can be used with not only dates but also numbers and decimals to covert them in desired format. Lets have a look at few queries:
TO_CHAR(3150.48, '9999.9') Result: ' 3150.4' TO_CHAR(-3150.48, '9999.9') Result: '-3150.4' TO_CHAR(3150.48, '$9,999.00') Result: ' $3150.48' TO_CHAR(3150.48, '9,999.99') Result: ' 3,150.48' TO_CHAR(48, '0099') Result: ' 0048'
2. TO_CHAR with SYSDATE
We can use TO_CHAR function to convert SYSDATE into desired date string format.
SELECT TO_CHAR( sysdate, 'MM-DD-YYYY' ) FROM dual;
Result:
Similarly, we can use TO_CHAR function to convert the date value into long date format. Lets see the query:
SELECT TO_CHAR( sysdate, 'MON DDth, YYYY' ) FROM dual;
Result:
We can use any date format from the table below to convert our query into desired date formats.
| Format | Description |
| YEAR | Year, spelled out |
| YYYY | 4-digit year |
| YYY YY Y | Last 3, 2, or 1 digit(s) of year. |
| IYY IY I | Last 3, 2, or 1 digit(s) of ISO year. |
| IYYY | 4-digit year based on the ISO standard |
| Q | Quarter of year (1, 2, 3, 4; JAN-MAR = 1). |
| MM | Month (01-12; JAN = 01). |
| MON | Abbreviated name of month. |
| MONTH | Name of month, padded with blanks to length of 9 characters. |
| WW | Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. |
| W | Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. |
| D | Day of week (1-7). |
| DAY | Name of day. |
| DD | Day of month (1-31). |
| DDD | Day of year (1-366). |
| HH | Hour of day (1-12). |
| HH12 | Hour of day (1-12). |
| HH24 | Hour of day (0-23). |
| MI | Minute (0-59). |
| SS | Seconds (0-59). |
3. TO_CHAR for formatting Date values in a column
TO_CHAR function can be used for formatting date values in a column and converting them in any Oracle recognized date format.
Lets see a query to understand this better:
SELECT first_name, last_name, hire_date, TO_CHAR( hire_date, 'dd-mm-yyyy' ) format_1, TO_CHAR( hire_date, 'ddth-mm-yyyy' ) format_2 FROM employees;
Result:
The query converts the date values in hire_date column into 2 string formats. We can use any format from the Oracle recognized date formats for conversion.
In this article, we saw how to use TO_CHAR function to convert date values into string.