The Oracle TO_CHAR function is one of the most common and useful string manipulation functions in Oracle. In this article and video, I’ll explain how to use the TO_CHAR function.
Purpose of the Oracle TO_CHAR Function
The purpose of the Oracle TO_CHAR function is to convert either a number or a date value to a string value. It works similar to the TO_DATE function and TO_NUMBER function. This function takes either a number or a date as an input, and converts it to a string value to be displayed or processed accordingly. The converted data is a VARCHAR2 data type.
The input value can be a numeric type of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or it can be a date type of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE.
This function also exists in PostgreSQL.
Syntax
The syntax of the Oracle TO_CHAR function is:
TO_CHAR( input_value, [format_mask], [nls_parameter] )
Parameters
The parameters of the TO_CHAR function are:
- input_value (mandatory): This is the value to convert into a String type. This is the main input to the function, and this value can either be a DATE type or any of the Number types.
- format_mask (optional): This is the format that the input_value should be displayed as. If this is omitted, the function will use a default format, as mentioned below.
- nls_parameter (optional): This value is used to determine how the output value is displayed.
If the format_mask parameter is omitted, then:
- DATE values are converted to values in the default date format.
- TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE values are converted to values in the default timestamp format.
- TIMESTAMP WITH TIME ZONE values are converted to values in the default timestamp with time zone format.
- Numeric values are converted to a string value that is long enough to hold its significant digits.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Format Mask
The format mask is helpful as it allows you to specify what format the input_value should be converted to.
To make it clear, we need to specify some values for the format mask. The possible values are:
Year
Parameter | Explanation |
YEAR | Year, spelled out in full words |
YYYY | 4-digit year |
YYY | Last 3 digits of year |
YY | Last 2 digits of year |
Y | Last digit of year |
IYY | Last 3 digits of ISO year |
IY | Last 2 digits of ISO year |
I | Last digit of ISO year |
IYYY | 4-digit year, which is based on the ISO standard |
RRRR | This format accepts a 2-digit year, and returns a 4-digit year. If the provided value is between 0 and 49, it will return a year greater than or equal to 2000. If the provided value is between 50 and 99, it will return a year less than 2000 |
Month
Parameter | Explanation |
Q | Quarter of year, from 1 to 4. JAN to MAR = 1 |
MM | Month, from 01 to 12. JAN = 01 |
MON | Abbreviated name of month. |
MONTH | Name of month, padded with blanks to length of 9 characters. |
RM | Roman numeral month, from I to XII. JAN = I. |
Week
Parameter | Explanation |
WW | Week of year, from 1 to 53. Week 1 starts on the first day of the year, and continues to the seventh day of the year. |
W | Week of month, from 1 to 5. Week 1 starts on the first day of the month and ends on the seventh. |
IW | Week of year, from 1 to 52 or 1 to 53, based on the ISO standard. |
Day
Parameter | Explanation |
D | Day of week, from 1 to 7. |
DAY | Name of day. |
DD | Day of month, from 1 to 31. |
DDD | Day of year, from 1 to 366. |
DY | Abbreviated name of day. |
J | Julian day, which is the number of days since January 1, 4712 BC. |
Time
Parameter | Explanation |
HH | Hour of day, from 1 to 12. |
HH12 | Hour of day, from 1 to 12. |
HH24 | Hour of day, from 0 to 23. |
MI | Minute, from 0 to 59 |
SS | Second, from 0 to 59 |
SSSSS | Seconds past midnight, from 0 to 86399. |
FF | Fractional seconds. This uses a value from 1 to 9 after FF, to indicate the number of digits in the fractional seconds (e.g. FF7) |
Indicators
Parameter | Explanation |
AM, A.M., PM, or P.M. | Meridian indicator |
AD or A.D | AD indicator |
BC or B.C. | BC indicator |
TZD | Daylight savings information |
TZH | Time zone hour. |
TZM | Time zone minute. |
TZR | Time zone region. |
The format mask also means that punctuation and quoted text is reproduced in the result. So, if you add an underscore (_) or a dash (-), for example, it is shown in the output.
NLS_DATE_LANGUAGE Explanation
The NLS_DATE_LANGUAGE can be specified in this function to determine how the date is shown. As you may know, different regions show the same date in different ways:
- China: yyyy-mm-dd
- UK: dd/mm/yyyy
- US: mm/dd/yyyy
To use the NLS_DATE_LANGUAGE as part of this function, you can use any language listed under Table A-1 on the Oracle Language page. Some examples are American, English, French, German, and Swedish.
Examples of the TO_CHAR Function
Here are some examples of the TO_CHAR function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1
This example converts the number 12345.67 into the format specified in the second parameter.
SELECT TO_CHAR(12345.67, '99999.9') FROM DUAL;
Result:
12345.7
As you can see, the result only has one decimal place. This is because the output format only had one decimal place, and the .67 was rounded up to .7.
Example 2
This next example converts the number 12345 into a string with zeroes in it.
SELECT TO_CHAR(12345, '00000000') FROM DUAL;
Result:
00012345
This is the result because the format mask of 0 means that there are leading zeroes added (to the left of the number) when it is converted.
Example 3
This example converts the current date (SYSDATE) into a string, using a format of 4 digit year, then an underscore, then 2 digit month, underscore, then two digit day.
SELECT TO_CHAR(SYSDATE, 'YYYY_MM_DD') FROM DUAL;
Result:
2014_12_27
Example 4
This example converts the SYSDATE value again, but uses the format mask of “Month” which is the full month name.
SELECT TO_CHAR(SYSDATE, 'Month') FROM DUAL;
Result:
December
This is because the Month of SYSDATE is December, and that is what gets converted.
Similar Functions
Some functions which are similar to the TO_CHAR function are:
- TO_DATE – converts a string value to a DATE type.
- TO_NUMBER – converts a value to a NUMBER type
- CAST – used for many types, including dates
You can find a full list of Oracle functions here.
While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:
Can we change the format to a specific date instead of sysdate using to_char ?
Yes, you can, you specify the date format as one of the parameters.