Overview
TO_CHAR is an SQL function that converts a date, time, or numeric value into a formatted string. It allows you to customize the display of these data types according to your preferences or specific requirements. In simpler terms, TO_CHAR helps you transform numbers and dates into text formats that are more readable and user-friendly.
Understanding the Syntax
The basic syntax for TO_CHAR is as follows:
- value: This is the date, time, or number that you want to convert.
- format: A string that specifies how you want the output formatted. It uses special characters to represent different date and time components or numeric formats.
Format Parameters
Here's an overview of some commonly used format elements for each data type:
Date and Time Format Elements:
- Year:
- YYYY: Four-digit year (e.g., 2025)
- YY: Two-digit year (e.g., 25)
- Month:
- MM: Two-digit month (01-12)
- MONTH: Full month name (e.g., JANUARY)
- MON: Abbreviated month name (e.g., JAN)
- Day:
- DD: Two-digit day of the month (01-31)
- DAY: Full day of the week name (e.g., MONDAY)
- DY: Abbreviated day of the week (e.g., MON)
- Time:
- HH24: Hour of the day (00-23)
- HH12: Hour of the day (01-12)
- MI: Minutes (00-59)
- SS: Seconds (00-59)
- AM: AM/PM indicator
Numeric Format Elements:
- 9: Represents a digit. You can use multiple 9s to accommodate numbers of varying lengths.
- 0: Indicates a digit, but leading zeros are displayed for numbers with fewer digits.
- ,: Thousands separator.
- .: Decimal point.
For more information about the format parameters of TO_CHAR function, you can check SuiteAnswers 50499: Sample Functions of TO_CHAR.
Here are some examples to illustrate the use of these format elements:
Example 1: Date Formatting
TO_CHAR({today}, 'DD-MON-YYYY')
This will display '22-MAY-2025' in the search results.
Example 2: Time Formatting
TO_CHAR({today}, 'HH12:MI AM')
This will display '03:22 AM' in the search results.
Example 3: Numeric Formatting
TO_CHAR({amount}, '9,999,999.99')
This will display '10,000.00' in the search results.
TO_CHAR({amount}, '0,000,000.00')
This will display '0,010,000.00' in the search results.
The format elements provided above are just a selection of the most commonly used ones. TO_CHAR offers a comprehensive set of formatting options to cater to various data presentation needs. It is essential to choose the right format elements based on your specific requirements and the type of data you are working with.
-----
If you find this information useful, let us know by reacting or commenting on this post!