SQL Date Format Guide — Format Dates in MySQL, PostgreSQL, SQL Server & Oracle

1. MySQL DATE_FORMAT — Format Dates and Datetimes

MySQL uses the DATE_FORMAT() function to format sql datetime format values. It takes a date/datetime expression and a format string made of specifiers.

Common MySQL DATE_FORMAT Specifiers

MySQL Date Format Examples

-- ISO date: 2026-08-04
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

-- dd/mm/yyyy format
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y');
-- Result: 04/08/2026

-- Full readable date
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');
-- Result: Tuesday, August 04, 2026

-- Date and time with 12-hour clock
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s %p');
-- Result: 2026-08-04 02:30:45 PM

-- Month abbreviation + 2-digit year
SELECT DATE_FORMAT(NOW(), '%b %y');
-- Result: Aug 26

MySQL tip: DATE_FORMAT works with both DATE and DATETIME columns. If you pass a DATE (no time), the time specifiers (%H, %i, %s) return 00.

2. PostgreSQL TO_CHAR — Date Formatting

PostgreSQL uses TO_CHAR() for all date and timestamp formatting. The format patterns are similar to Oracle's but with some PostgreSQL-specific additions.

PostgreSQL TO_CHAR Patterns

PostgreSQL Date Format Examples

-- ISO date
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
-- Result: 2026-08-04

-- dd/mm/yyyy (European format)
SELECT TO_CHAR(NOW(), 'DD/MM/YYYY');
-- Result: 04/08/2026

-- Full text date
SELECT TO_CHAR(NOW(), 'Day, Month DD, YYYY');
-- Result: Tuesday , August  04, 2026

-- Timestamp with timezone
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS TZ');
-- Result: 2026-08-04 14:30:45 UTC

-- Quarter and day of year
SELECT TO_CHAR(NOW(), 'YYYY-Q') AS quarter,
       TO_CHAR(NOW(), 'DDD') AS day_of_year;
-- Result: 2026-3 | 216

3. SQL Server Date Format — FORMAT and CONVERT

Date format in SQL Server has two main approaches: the modern FORMAT() function (SQL Server 2012+) and the legacy CONVERT() with style codes. FORMAT uses .NET format strings and is more flexible, while CONVERT is faster for pre-defined styles.

SQL Server FORMAT Function

-- ISO date
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
-- Result: 2026-08-04

-- dd/mm/yyyy (UK/European style)
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');
-- Result: 04/08/2026

-- Full date and time
SELECT FORMAT(GETDATE(), 'dddd, MMMM dd, yyyy HH:mm:ss');
-- Result: Tuesday, August 04, 2026 14:30:45

-- Custom: year-month only
SELECT FORMAT(GETDATE(), 'yyyy-MM');
-- Result: 2026-08

SQL Server CONVERT Style Codes

-- Using CONVERT with style
SELECT CONVERT(varchar, GETDATE(), 103) AS uk_date;
-- Result: 04/08/2026

SELECT CONVERT(varchar, GETDATE(), 112) AS iso_date;
-- Result: 20260804

SELECT CONVERT(varchar, GETDATE(), 120) AS datetime_iso;
-- Result: 2026-08-04 14:30:45

Performance note: FORMAT() is more readable and flexible but slower than CONVERT() on large datasets. For formatting millions of rows, prefer CONVERT() with style codes or do formatting in your application layer.

4. Oracle Date Format — TO_CHAR and NLS

Date format Oracle relies on the TO_CHAR() function with format masks. Oracle also respects the session-level NLS_DATE_FORMAT parameter, which controls the default display format.

Oracle TO_CHAR Format Masks

-- ISO date
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
-- Result: 2026-08-04

-- dd/mm/yyyy
SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') FROM DUAL;
-- Result: 04/08/2026

-- Oracle default style (DD-MON-YY)
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
-- Result: 04-AUG-2026

-- Full date with time
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- Result: 2026-08-04 14:30:45

-- Day name and month name
SELECT TO_CHAR(SYSDATE, 'Day, Month DD, YYYY') FROM DUAL;
-- Result: Tuesday , August  04, 2026

Changing Oracle Default Date Format

-- Session level
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- Check current setting
SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

Oracle gotcha: Oracle's DATE type always includes a time component. When you insert DATE '2026-08-04', the time defaults to midnight (00:00:00). Use TRUNC(SYSDATE) to strip the time portion.

5. Convert String to Date Across Databases

Converting a string to a date — often searched as "sql cast string to date" — works differently in each database. Here is how each engine handles it.

MySQL STR_TO_DATE

-- Convert dd/mm/yyyy string to DATE
SELECT STR_TO_DATE('04/08/2026', '%d/%m/%Y');
-- Result: 2026-08-04 (DATE type)

-- Convert with time
SELECT STR_TO_DATE('04-08-2026 14:30:45', '%d-%m-%Y %H:%i:%s');

PostgreSQL TO_DATE and CAST

-- Using TO_DATE
SELECT TO_DATE('04/08/2026', 'DD/MM/YYYY');
-- Result: 2026-08-04

-- Using CAST (ISO format only)
SELECT CAST('2026-08-04' AS DATE);

-- Using :: operator
SELECT '2026-08-04'::DATE;

-- With timestamp
SELECT TO_TIMESTAMP('04/08/2026 14:30', 'DD/MM/YYYY HH24:MI');

SQL Server CAST and CONVERT

-- ISO format string (always works)
SELECT CAST('2026-08-04' AS DATE);

-- dd/mm/yyyy string with CONVERT style
SELECT CONVERT(DATE, '04/08/2026', 103);
-- Style 103 = dd/mm/yyyy

-- mm/dd/yyyy string
SELECT CONVERT(DATE, '08/04/2026', 101);
-- Style 101 = mm/dd/yyyy

-- TRY_CONVERT (returns NULL instead of error)
SELECT TRY_CONVERT(DATE, 'invalid-date', 103);
-- Result: NULL

Oracle TO_DATE

-- Convert string to date
SELECT TO_DATE('04/08/2026', 'DD/MM/YYYY') FROM DUAL;

-- With time
SELECT TO_DATE('2026-08-04 14:30:45', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- Handle invalid dates with DEFAULT ON CONVERSION ERROR (12cR2+)
SELECT TO_DATE('invalid' DEFAULT '2026-01-01' ON CONVERSION ERROR,
              'DD/MM/YYYY') FROM DUAL;

6. Date Format Best Practices

Always Store Dates as DATE Type

Never store dates as VARCHAR. It breaks indexing, prevents date arithmetic, and makes formatting inconsistent. Every database has a proper date type — use it.

Use ISO 8601 for Data Exchange

When passing dates between systems (APIs, CSV exports, ETL pipelines), use YYYY-MM-DD (ISO 8601). It is unambiguous — 2026-08-04 is always August 4th, regardless of locale.

Format in the Application Layer

For web applications, return dates as ISO strings from the database and format them to the user's locale in your frontend (JavaScript Intl.DateTimeFormat, Python strftime, etc.). This keeps your SQL portable and your UI flexible.

Be Explicit About Time Zones

Use TIMESTAMPTZ (PostgreSQL), DATETIMEOFFSET (SQL Server), or TIMESTAMP WITH TIME ZONE (Oracle) when timezone awareness matters. Avoid relying on the server's default timezone — it changes between environments.

Cross-Database Cheat Sheet

FAQ