TO_CHAR()
SQL · Date Functions
Theory
TO_CHAR does the opposite of TO_DATE: it turns a date (or number) into formatted text. TO_CHAR(hire_date, 'DD-Mon-YYYY') on 2023-07-01 gives '01-Jul-2023', and 'Month YYYY' gives 'July 2023'. The format mask controls the output: DD for day, MM for month number, Mon or Month for the name, YYYY for year, HH24:MI for time.
TO_CHAR is available in Oracle, PostgreSQL and Snowflake. MySQL uses DATE_FORMAT(d, '%d-%b-%Y'), SQL Server uses FORMAT(d, 'dd-MMM-yyyy') or CONVERT with a style number, and SQLite uses strftime('%d-%m-%Y', d), which has no month-name code. The key thing to remember is that the result is text, not a date: it is perfect for labels in a report or an export file, but if you sort or compare it, '01-Jul-2023' and '15-Mar-2021' will be ordered alphabetically, not by time.
Why we use it
People read dates in local styles (08-11-2026, Nov 2026), but databases store them in one internal form. TO_CHAR formats dates exactly as a report, invoice or export file needs them.
When to use it
Use it at the very end, for display, labels or file exports. Do not use it for filtering, sorting or grouping by time; keep the real date (or DATE_TRUNC) for that and format only in the final SELECT.
How it works
- Real DATE value, e.g. 2023-07-01
- Pick the display mask: 'DD-MM-YYYY', 'Mon YYYY'
- Database fills each code with the date's parts
- Return a text string such as '01-07-2023'
- Still need to sort by date? Yes: Sort by the original date column. No: Use the text for display.
- Human-friendly date labels
Syntax
TO_CHAR(date, 'format')Example
SELECT TO_CHAR(hire_date, 'DD-Mon-YYYY') FROM employees;Key points
- Oracle/Postgres function for flexible date/number formatting
- SQLite’s equivalent is strftime() with %d/%m/%Y-style codes
- MySQL: DATE_FORMAT(d, '%d-%b-%Y'); SQL Server: FORMAT(d, 'dd-MMM-yyyy')
- Output is text — sorting it sorts alphabetically, not by time
- Format only in the final SELECT; filter and group on real dates