Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL TO_CHAR (Formatting Dates)

PostgreSQL's date functions, such as `TO_CHAR`, are used for formatting dates and times into a specified textual representation. `TO_CHAR` is particularly useful for converting date/time data types into formatted strings.

Usage

The `TO_CHAR` function is used to transform date/time values into a string with a specific format, which is especially useful for displaying dates in a user-friendly manner. It requires a date/time value and a format string that defines the desired output format.

sql
TO_CHAR(date_value, 'format_string')

In this syntax, `date_value` is the date or time you want to format, and `'format_string'` specifies how you want it to appear, using specific format patterns like `YYYY`, `MM`, `DD`, etc. Note that the `format_string` is case-sensitive, which is crucial for ensuring correct output.

Examples

1. Basic Date Formatting

sql
SELECT TO_CHAR(current_date, 'YYYY-MM-DD');

This query formats the current date as a string in the `YYYY-MM-DD` format, displaying the full year, month, and day.

2. Custom Date and Time Formatting

sql
SELECT TO_CHAR(current_timestamp, 'DD Mon YYYY, HH12:MI AM');

This example converts the current timestamp into a more readable format, displaying the day, abbreviated month, full year, hour, and minutes with an AM/PM indicator.

3. Formatting with Day Name

sql
SELECT TO_CHAR(current_date, 'Day, DDth Month YYYY');

This example formats the date to include the full day name, the day with an ordinal suffix, and the full month name, creating a more verbose and human-readable date string.

Tips and Best Practices

  • Choose appropriate format patterns. Use format patterns that match your locale and audience preferences. Consider using patterns that handle international date formats effectively.
  • Be mindful of padding. The `TH` pattern for ordinal suffixes and `FM` to suppress padding can make strings more readable. `FM` (fill mode) removes leading and trailing spaces, providing a cleaner output.
  • Use for presentation, not storage. `TO_CHAR` should be used for presentation purposes; store dates in standard date/time formats.
  • Test format strings. Always test format strings to ensure they produce the desired output, especially when using complex patterns.

Common Mistakes and Troubleshooting

  • Case Sensitivity Errors: Ensure your `format_string` uses the correct case for each pattern. A common mistake is using lowercase where uppercase is required, leading to unexpected results.
  • Example Error: If you encounter an error such as "function to_char(unknown, unknown) is not unique," verify that the `date_value` and `format_string` are correctly specified and of the expected types.

In addition to dates, `TO_CHAR` can also format numeric values, enhancing its versatility beyond date/time formatting.