DATE FORMATFunctions
Buy Me A Coffee

Format a date or timestamp value as a string in a specific display format. Every engine has a different function name and format code system.

Formats a date or timestamp into a string using format specifiers. MySQL uses DATE_FORMAT(), SQLite uses STRFTIME(), SQL Server uses FORMAT(). Useful for generating human-readable date strings in queries.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all DATE_FORMAT(date, format) uses % format codes: %Y (4-digit year), %m (month 01-12), %d (day 01-31), %H (hour 00-23), %i (minute), %s (second). TIME_FORMAT() for time-only values.
PostgreSQL ✓ Supported all TO_CHAR(timestamp, format) uses its own format codes: YYYY (year), MM (month), DD (day), HH24 (hour), MI (minute), SS (second). TO_DATE() and TO_TIMESTAMP() for parsing. Very rich format string options including ordinals (DDth), spelled-out months (Month), etc.
SQL Server ✓ Supported 2012 FORMAT(value, format [, culture]) uses .NET format strings. Flexible but slow on large datasets — avoid in WHERE clauses or high-volume queries. Pre-2012: use CONVERT(VARCHAR, date, style) with numeric style codes (103 = dd/mm/yyyy, 120 = yyyy-mm-dd hh:mi:ss).
Oracle ✓ Supported all TO_CHAR(date, format) using Oracle format codes: YYYY, MM, DD, HH24, MI, SS. TO_DATE(str, format) for parsing. Format codes differ from PostgreSQL's despite the same function name — Oracle uses HH24 for 24h hour, PostgreSQL also uses HH24, so that one is compatible.
SQLite ✓ Supported all strftime(format, date) uses % format codes similar to C's strftime: %Y, %m, %d, %H, %M, %S. date() and time() return fixed ISO 8601 formats. No TO_CHAR or DATE_FORMAT.

Details

Date formatting is almost entirely non-portable. MySQL uses % codes (DATE_FORMAT), PostgreSQL and Oracle both use TO_CHAR but with slightly different code sets, SQL Server's FORMAT() uses .NET strings, and SQLite uses C-style strftime. The only safe portable path is formatting dates in application code rather than SQL.

Standard Syntax

-- No cross-engine standard exists. -- Common approaches: FORMAT(date_col, 'yyyy-MM-dd') -- SQL Server TO_CHAR(date_col, 'YYYY-MM-DD') -- Oracle / PostgreSQL DATE_FORMAT(date_col, '%Y-%m-%d') -- MySQL

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since 2012 Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

DATE_FORMAT(date, format) uses % format codes: %Y (4-digit year), %m (month 01-12), %d (day 01-31), %H (hour 00-23), %i (minute), %s (second). TIME_FORMAT() for time-only values.

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d'); -- '2024-03-15' SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- '15/03/2024' SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- '2024-03-15 14:30:00' SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y'); -- 'Friday, March 15, 2024'

PostgreSQL

TO_CHAR(timestamp, format) uses its own format codes: YYYY (year), MM (month), DD (day), HH24 (hour), MI (minute), SS (second). TO_DATE() and TO_TIMESTAMP() for parsing. Very rich format string options including ordinals (DDth), spelled-out months (Month), etc.

SELECT TO_CHAR(NOW(), 'YYYY-MM-DD'); -- '2024-03-15' SELECT TO_CHAR(NOW(), 'DD/MM/YYYY'); -- '15/03/2024' SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- '2024-03-15 14:30:00' SELECT TO_CHAR(NOW(), 'FMMonth DDth, YYYY'); -- 'March 15th, 2024'

SQL Server

FORMAT(value, format [, culture]) uses .NET format strings. Flexible but slow on large datasets — avoid in WHERE clauses or high-volume queries. Pre-2012: use CONVERT(VARCHAR, date, style) with numeric style codes (103 = dd/mm/yyyy, 120 = yyyy-mm-dd hh:mi:ss).

SELECT FORMAT(GETDATE(), 'yyyy-MM-dd'); -- '2024-03-15' SELECT FORMAT(GETDATE(), 'dd/MM/yyyy'); -- '15/03/2024' SELECT FORMAT(GETDATE(), 'D', 'en-US'); -- 'Friday, March 15, 2024' -- Pre-2012 / high-performance alternative: SELECT CONVERT(VARCHAR, GETDATE(), 120); -- '2024-03-15 14:30:00'

Oracle

TO_CHAR(date, format) using Oracle format codes: YYYY, MM, DD, HH24, MI, SS. TO_DATE(str, format) for parsing. Format codes differ from PostgreSQL's despite the same function name — Oracle uses HH24 for 24h hour, PostgreSQL also uses HH24, so that one is compatible.

SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; -- '2024-03-15' SELECT TO_CHAR(SYSDATE, 'DD/MM/YYYY') FROM DUAL; -- '15/03/2024' SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; SELECT TO_CHAR(SYSDATE, 'Day, Month DD YYYY') FROM DUAL;

SQLite

strftime(format, date) uses % format codes similar to C's strftime: %Y, %m, %d, %H, %M, %S. date() and time() return fixed ISO 8601 formats. No TO_CHAR or DATE_FORMAT.

SELECT strftime('%Y-%m-%d', 'now'); -- '2024-03-15' SELECT strftime('%d/%m/%Y', 'now'); -- '15/03/2024' SELECT strftime('%Y-%m-%d %H:%M:%S', 'now'); -- '2024-03-15 14:30:00' SELECT date('now'); -- '2024-03-15' (ISO 8601 only)