Converting a date into dd mm yyyy format is one of the most common requests in SQL, and also one of the most misunderstood. Many developers start with CAST(date_column AS VARCHAR) expecting a formatted result, only to get something like 2024-03-15 instead of 15 03 2024. The reason is simple: CAST changes the data type, not the display format. To control how a date looks, you need a formatting function specific to your database engine. This guide walks through exactly how to SQL cast date as dd mm yyyy across SQL Server, MySQL, PostgreSQL, Oracle, and SQLite, along with the pitfalls that quietly break reports and dashboards.
No fluff here — just what actually works.
Why CAST Alone Cannot Format a Date
In standard SQL, CAST() is a type-conversion operator. When you write:
SELECT CAST(order_date AS VARCHAR) FROM orders;
the database converts the internal date value into a character string using its default session format, not a format you choose. Consider this: that default is usually ISO 8601 (YYYY-MM-DD) because it sorts correctly and avoids ambiguity. There is no CAST(date AS 'dd mm yyyy') syntax in any major engine Not complicated — just consistent. Turns out it matters..
So the practical rule is:
- CAST is for changing types (date → string, string → date).
- CONVERT / FORMAT / TO_CHAR / DATE_FORMAT / strftime are for controlling the output pattern.
Once you separate those two ideas, the rest becomes straightforward That's the whole idea..
SQL Server: CONVERT and FORMAT
SQL Server has two reliable options for producing dd mm yyyy.
Option 1: CONVERT with a style code
SELECT CONVERT(VARCHAR(10), order_date, 105) AS formatted_date
FROM orders;
Style 105 returns dd-mm-yyyy. Style 103 returns dd/mm/yyyy. If you genuinely need spaces, wrap it:
SELECT REPLACE(CONVERT(VARCHAR(10), order_date, 105), '-', ' ') AS formatted_date
FROM orders;
CONVERT is fast, deterministic, and works on every SQL Server version.
Option 2: FORMAT (SQL Server 2012 and later)
SELECT FORMAT(order_date, 'dd MM yyyy') AS formatted_date
FROM orders;
FORMAT uses .NET format strings, so it is flexible and culture-aware. The trade-off is performance: FORMAT is significantly slower than CONVERT on large result sets because it calls into the CLR. Use it for small outputs, reports, or ad-hoc queries — not for millions of rows.
MySQL: DATE_FORMAT
MySQL does not use CAST for formatting either. The function you want is DATE_FORMAT:
SELECT DATE_FORMAT(order_date, '%d %m %Y') AS formatted_date
FROM orders;
Key pattern tokens:
%d— day with leading zero (01–31)%m— month with leading zero (01–12)%Y— four-digit year%y— two-digit year%M— full month name (January)%b— abbreviated month (Jan)
So '%d %m %Y' gives 15 03 2024, while '%d-%m-%Y' gives 15-03-2024.
PostgreSQL: TO_CHAR
PostgreSQL uses TO_CHAR for output formatting:
SELECT TO_CHAR(order_date, 'DD MM YYYY') AS formatted_date
FROM orders;
Note that PostgreSQL pattern letters are case-sensitive:
DD— day of monthMM— month numberYYYY— four-digit yearMON— abbreviated month nameMonth— full month name, padded
A common mistake is writing to_char(order_date, 'dd mm yyyy'), which returns lowercase or unexpected output. Always use uppercase pattern tokens.
Oracle: TO_CHAR with NLS Awareness
Oracle follows the same idea:
SELECT TO_CHAR(order_date, 'DD MM YYYY') AS formatted_date
FROM orders;
Oracle is sensitive to NLS_DATE_LANGUAGE and NLS_DATE_FORMAT session settings. If you need month names in a specific language, specify the third argument:
SELECT TO_CHAR(order_date, 'DD MM YYYY', 'NLS_DATE_LANGUAGE=ENGLISH')
FROM orders;
SQLite: strftime
SQLite stores dates as text, real, or integer, and formats them with strftime:
SELECT strftime('%d %m %Y', order_date) AS formatted_date
FROM orders;
Because SQLite is dynamically typed, CAST(order_date AS TEXT) will simply return whatever string is stored — which may already be in a different format. Always normalize on insert That alone is useful..
Converting a dd mm yyyy String Back Into a Date
Formatting is only half the job. You also need to parse user input like 15 03 2024 into a real date value.
| Database | Parsing function |
|---|---|
| SQL Server | CONVERT(DATE, '15 03 2024', 105) after replacing spaces, or PARSE |
| MySQL | STR_TO_DATE('15 03 2024', '%d %m %Y') |
| PostgreSQL | TO_DATE('15 03 2024', 'DD MM YYYY') |
| Oracle | `TO_DATE('15 03 2024', 'DD MM YYYY |