SQL - Convert Date to String | Application Architect

Key Insights

Every major database system handles date-to-string conversion differently—SQL Server uses CONVERT and FORMAT, MySQL uses DATE_FORMAT, while PostgreSQL and Oracle both use TO_CHAR with different syntax rules.
The FORMAT function in SQL Server and TO_CHAR in PostgreSQL/Oracle offer the most flexibility, but CONVERT with style codes often performs better in high-volume scenarios.
Always use explicit conversion functions rather than relying on implicit casting—it makes your code portable, predictable, and easier to maintain across database migrations.

Why Date-to-String Conversion Matters

Converting dates to strings is one of those tasks that seems trivial until you’re debugging a report that shows “2024-01-15” in production but “01/15/2024” in development. Date formatting affects user-facing reports, data exports, API responses, log files, and any scenario where you need to concatenate dates with other text.

The challenge? Every database vendor implemented their own approach. SQL Server developers reaching for DATE_FORMAT will hit a syntax error. PostgreSQL developers expecting CONVERT will be equally disappointed. Understanding the native functions for your database—and knowing the portable alternatives—saves debugging time and prevents production surprises.

Let’s walk through each major database system’s approach, with practical examples you can use immediately.

SQL Server: CONVERT and FORMAT Functions

SQL Server gives you two primary options: the legacy CONVERT function with numeric style codes, and the newer FORMAT function that uses .NET format strings.

The CONVERT Function

CONVERT has been around since the early days of SQL Server. It uses numeric style codes to determine output format:

-- Basic syntax: CONVERT(data_type, expression, style)

-- Style 101: US format (MM/DD/YYYY)
SELECT CONVERT(VARCHAR(10), GETDATE(), 101);
-- Output: 01/15/2024

-- Style 103: British/French format (DD/MM/YYYY)
SELECT CONVERT(VARCHAR(10), GETDATE(), 103);
-- Output: 15/01/2024

-- Style 120: ODBC canonical (YYYY-MM-DD HH:MI:SS)
SELECT CONVERT(VARCHAR(19), GETDATE(), 120);
-- Output: 2024-01-15 14:30:45

-- Style 112: ISO compact (YYYYMMDD) - great for file names
SELECT CONVERT(VARCHAR(8), GETDATE(), 112);
-- Output: 20240115

-- Style 107: Month abbreviated (Mon DD, YYYY)
SELECT CONVERT(VARCHAR(12), GETDATE(), 107);
-- Output: Jan 15, 2024

The numeric style codes are cryptic but fast. Memorize the ones you use frequently: 101 for US dates, 120 for ISO timestamps, 112 for compact dates.

The FORMAT Function

Introduced in SQL Server 2012, FORMAT uses .NET format strings and is far more readable:

-- Basic syntax: FORMAT(value, format_string, culture)

-- ISO format
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd');
-- Output: 2024-01-15

-- Full month name with ordinal-style day
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy');
-- Output: January 15, 2024

-- Custom format with day name
SELECT FORMAT(GETDATE(), 'dddd, MMMM d, yyyy');
-- Output: Monday, January 15, 2024

-- Time with AM/PM
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd hh:mm:ss tt');
-- Output: 2024-01-15 02:30:45 PM

-- Culture-specific formatting
SELECT FORMAT(GETDATE(), 'd', 'de-DE');
-- Output: 15.01.2024

Performance warning: FORMAT is significantly slower than CONVERT—sometimes 10x slower in large result sets. Use CONVERT for bulk operations and FORMAT when readability matters more than speed.

MySQL: DATE_FORMAT Function

MySQL consolidates date-to-string conversion into the DATE_FORMAT function, which uses percent-sign specifiers similar to C’s strftime:

-- Basic syntax: DATE_FORMAT(date, format)

-- ISO format
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');
-- Output: 2024-01-15

-- US format
SELECT DATE_FORMAT(NOW(), '%m/%d/%Y');
-- Output: 01/15/2024

-- Full month name
SELECT DATE_FORMAT(NOW(), '%M %d, %Y');
-- Output: January 15, 2024

-- Day name included
SELECT DATE_FORMAT(NOW(), '%W, %M %e, %Y');
-- Output: Monday, January 15, 2024

-- Full timestamp
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
-- Output: 2024-01-15 14:30:45

-- 12-hour format with AM/PM
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s %p');
-- Output: 2024-01-15 02:30:45 PM

-- Compact format for filenames
SELECT DATE_FORMAT(NOW(), '%Y%m%d_%H%i%s');
-- Output: 20240115_143045

Key specifiers to remember:

%Y = 4-digit year, %y = 2-digit year
%m = zero-padded month, %c = no padding
%d = zero-padded day, %e = no padding
%H = 24-hour, %h = 12-hour
%M = full month name, %b = abbreviated

PostgreSQL: TO_CHAR Function

PostgreSQL uses TO_CHAR for date formatting with template patterns that read more naturally than MySQL’s specifiers:

-- Basic syntax: TO_CHAR(timestamp, format)

-- ISO format
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD');
-- Output: 2024-01-15

-- Full timestamp
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
-- Output: 2024-01-15 14:30:45

-- Full month name (note: includes padding spaces)
SELECT TO_CHAR(NOW(), 'Month DD, YYYY');
-- Output: January   15, 2024

-- Use FM prefix to remove padding
SELECT TO_CHAR(NOW(), 'FMMonth DD, YYYY');
-- Output: January 15, 2024

-- Day name
SELECT TO_CHAR(NOW(), 'FMDay, FMMonth DD, YYYY');
-- Output: Monday, January 15, 2024

-- 12-hour format with AM/PM
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH12:MI:SS AM');
-- Output: 2024-01-15 02:30:45 PM

-- Ordinal day suffix
SELECT TO_CHAR(NOW(), 'FMMonth DDth, YYYY');
-- Output: January 15th, 2024

-- Week number and quarter
SELECT TO_CHAR(NOW(), 'YYYY-"Q"Q-"W"IW');
-- Output: 2024-Q1-W03

The FM (fill mode) modifier is crucial in PostgreSQL—without it, month and day names include trailing spaces to maintain fixed-width output. Always use FM for variable-width display formatting.

Oracle: TO_CHAR Function

Oracle also uses TO_CHAR, with format models similar to PostgreSQL but with some Oracle-specific patterns:

-- Basic syntax: TO_CHAR(date, format_model)

-- ISO format
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
-- Output: 2024-01-15

-- Oracle's traditional format
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY') FROM DUAL;
-- Output: 15-JAN-2024

-- Full timestamp with 24-hour time
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- Output: 2024-01-15 14:30:45

-- Full month name
SELECT TO_CHAR(SYSDATE, 'MONTH DD, YYYY') FROM DUAL;
-- Output: JANUARY   15, 2024

-- Remove padding with FM
SELECT TO_CHAR(SYSDATE, 'FMMonth DD, YYYY') FROM DUAL;
-- Output: January 15, 2024

-- Day name with proper casing
SELECT TO_CHAR(SYSDATE, 'FMDay, FMMonth DD, YYYY') FROM DUAL;
-- Output: Monday, January 15, 2024

-- 12-hour format
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;
-- Output: 2024-01-15 02:30:45 PM

-- Spelled-out components
SELECT TO_CHAR(SYSDATE, 'FMDDSPTH "of" MONTH, YYYY') FROM DUAL;
-- Output: FIFTEENTH of JANUARY, 2024

Oracle’s SP suffix spells out numbers, and TH adds ordinal suffixes—useful for formal documents and reports.

CAST and Standard SQL Approaches

When you need basic ISO format output and want maximum portability, CAST works across all major databases:

-- Works in SQL Server, PostgreSQL, MySQL, Oracle
SELECT CAST(CURRENT_DATE AS VARCHAR(10));
-- Output varies: typically YYYY-MM-DD

-- String concatenation with dates (implicit conversion)
-- SQL Server
SELECT 'Report generated: ' + CONVERT(VARCHAR(10), GETDATE(), 120);

-- PostgreSQL/Oracle
SELECT 'Report generated: ' || TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD');

-- MySQL
SELECT CONCAT('Report generated: ', DATE_FORMAT(NOW(), '%Y-%m-%d'));

The catch: CAST output format depends on database settings and isn’t guaranteed to be consistent. Use explicit formatting functions when the output format matters.

Quick Reference Table

Format	SQL Server	MySQL	PostgreSQL	Oracle
Function	CONVERT / FORMAT	DATE_FORMAT	TO_CHAR	TO_CHAR
YYYY-MM-DD	CONVERT(VARCHAR, date, 120)	DATE_FORMAT(date, ‘%Y-%m-%d’)	TO_CHAR(date, ‘YYYY-MM-DD’)	TO_CHAR(date, ‘YYYY-MM-DD’)
MM/DD/YYYY	CONVERT(VARCHAR, date, 101)	DATE_FORMAT(date, ‘%m/%d/%Y’)	TO_CHAR(date, ‘MM/DD/YYYY’)	TO_CHAR(date, ‘MM/DD/YYYY’)
DD/MM/YYYY	CONVERT(VARCHAR, date, 103)	DATE_FORMAT(date, ‘%d/%m/%Y’)	TO_CHAR(date, ‘DD/MM/YYYY’)	TO_CHAR(date, ‘DD/MM/YYYY’)
Month DD, YYYY	FORMAT(date, ‘MMMM dd, yyyy’)	DATE_FORMAT(date, ‘%M %d, %Y’)	TO_CHAR(date, ‘FMMonth DD, YYYY’)	TO_CHAR(date, ‘FMMonth DD, YYYY’)
Full timestamp	CONVERT(VARCHAR, date, 120)	DATE_FORMAT(date, ‘%Y-%m-%d %H:%i:%s’)	TO_CHAR(date, ‘YYYY-MM-DD HH24:MI:SS’)	TO_CHAR(date, ‘YYYY-MM-DD HH24:MI:SS’)

When migrating between databases, this table is your friend. Bookmark it, print it, tattoo it on your forearm—whatever helps you avoid the inevitable “function does not exist” errors at 2 AM.