TO_DATE()
SQL · Date Functions
Theory
TO_DATE converts text into a real DATE using a format mask that tells the database how to read the text. TO_DATE('11-07-2026', 'DD-MM-YYYY') says 'the first two digits are the day, then the month, then a four-digit year', so the result is 11 July 2026. Without the mask, '11-07-2026' is ambiguous: an American reader would see 7 November.
TO_DATE exists in Oracle, PostgreSQL and Snowflake, with mask codes like DD, MM, MON, YYYY. MySQL uses STR_TO_DATE('11-07-2026', '%d-%m-%Y'), SQL Server uses CONVERT(date, '11-07-2026', 105) or TRY_CONVERT, and SQLite's date() only understands ISO text (YYYY-MM-DD), so you rearrange the pieces yourself with substr(). Once a value is a real date, sorting, comparing and date arithmetic work correctly, whereas text dates in DD-MM-YYYY order sort wrongly ('02-01-2026' comes before '11-07-2025').
Why we use it
Data from CSV files, forms and old systems often arrives with dates as text in local formats. TO_DATE turns that text into proper dates so you can filter, sort and do date math reliably.
When to use it
Use it when loading or cleaning text dates that are not in ISO format. If the text is already ISO (2026-07-11), a simple CAST usually works. For messy data, prefer a 'try' version (TRY_TO_DATE, TRY_CONVERT) that returns NULL instead of failing.
How it works
- Text value arrives, e.g. '11-07-2026'
- Supply the mask that matches it: 'DD-MM-YYYY'
- Database reads day, month, year from positions
- Text matches the mask and is valid? Yes: Return DATE 2026-07-11. No: Error (NULL with TRY_).
- Sort, compare and do date math safely
Syntax
TO_DATE(string, 'format')Example
SELECT TO_DATE('11-07-2026', 'DD-MM-YYYY');Key points
- Oracle/Postgres syntax shown with an explicit format mask
- SQLite expects ISO 8601 text (YYYY-MM-DD) with no format mask
- MySQL: STR_TO_DATE(s, '%d-%m-%Y'); SQL Server: CONVERT(date, s, 105)
- Text dates in DD-MM-YYYY order sort wrongly — convert before sorting
- Use TRY_ variants on dirty data so bad rows become NULL instead of failing