SQL Date Format Guide

January 14, 2026 By Matthew Rathbone

Date formatting turns a date into text in the layout you choose, such as 03/15/2026 or Thursday, January 15. Each database has its own function for it, and the examples cover PostgreSQL, MySQL, SQLite and SQL Server.

How to format dates in SQL

Give the function a date and a format string. Codes in the format string stand for parts of the date, and everything else is copied as it is. In SQLite the function is strftime(), and its codes start with %:

strftime(format, date)

SELECT strftime('%m/%d/%Y', '2026-03-15');
-- Output: 03/15/2026

SELECT strftime('%I:%M %p', '2026-03-15 14:30:00');
-- Output: 02:30 PM

On a column, the same date can come out in as many layouts as you need:

-- Input: orders
-- order_id | order_date
-- ---------+--------------------
-- 1        | 2026-01-15 09:42:00
-- 2        | 2026-03-02 16:05:00

SELECT
  order_date,
  strftime('%Y-%m-%d', order_date) AS iso_date,
  strftime('%m/%d/%Y', order_date) AS us_date,
  strftime('%d/%m/%Y', order_date) AS european_date
FROM orders;

-- Output:
-- order_date          | iso_date   | us_date    | european_date
-- --------------------+------------+------------+--------------
-- 2026-01-15 09:42:00 | 2026-01-15 | 01/15/2026 | 15/01/2026
-- 2026-03-02 16:05:00 | 2026-03-02 | 03/02/2026 | 02/03/2026

These are the codes you’ll use most, with what each one gives for 2026-03-15 14:30:45:

Code	Meaning	Example
`%Y`	4-digit year	2026
`%m`	Month (01-12)	03
`%d`	Day of month (01-31)	15
`%H`	Hour, 24-hour clock (00-23)	14
`%I`	Hour, 12-hour clock (01-12)	02
`%p`	AM or PM	PM
`%M`	Minute (00-59)	30
`%S`	Second (00-59)	45
`%w`	Day of week (0-6, Sunday is 0)	0

Rules to know

SQLite has no codes for day or month names. Codes such as %a and %B make strftime() return NULL. Map %w or %m to a name with CASE instead (see the day of the week section below).
The result is text. Comparing or sorting formatted dates compares strings, which only matches date order when the year comes first:

-- Input: sales
-- id | sale_date  | amount
-- ---+------------+-------
-- 1  | 2025-11-20 | 89.0
-- 2  | 2026-01-08 | 120.0
-- 3  | 2026-03-04 | 64.5

-- Wrong: '11/2025' > '02/2026' is true, because '1' > '0'
SELECT * FROM sales
WHERE strftime('%m/%Y', sale_date) > '02/2026';

-- Output:
-- id | sale_date  | amount
-- ---+------------+-------
-- 1  | 2025-11-20 | 89.0
-- 3  | 2026-03-04 | 64.5

-- Right: put the year first
SELECT * FROM sales
WHERE strftime('%Y-%m', sale_date) > '2026-02';

-- Output:
-- id | sale_date  | amount
-- ---+------------+-------
-- 3  | 2026-03-04 | 64.5

NULL in, NULL out. strftime('%Y-%m-%d', NULL) returns NULL. Wrap it in COALESCE() to show something else, such as 'No date'.
Filter on the date, format it for display. WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01' returns the same rows as WHERE strftime('%Y', order_date) = '2026', but only the first can use an index on order_date.

Alternative syntax

PostgreSQL, MySQL and SQL Server each have their own function and codes:

-- PostgreSQL: TO_CHAR(value, pattern)
-- (FM stops it padding the day name to 9 characters)
SELECT TO_CHAR(TIMESTAMP '2026-01-15 09:42:00', 'FMDay, MM/DD/YYYY HH24:MI');
-- Output: Thursday, 01/15/2026 09:42

-- MySQL: DATE_FORMAT(value, pattern)
-- (%i is the minute; %M would be the month name)
SELECT DATE_FORMAT('2026-01-15 09:42:00', '%W, %m/%d/%Y %H:%i');
-- Output: Thursday, 01/15/2026 09:42

-- SQL Server: FORMAT(value, pattern)
-- (MM is the month, mm the minute)
SELECT FORMAT(CAST('2026-01-15 09:42:00' AS datetime2), 'dddd, MM/dd/yyyy HH:mm');
-- Output: Thursday, 01/15/2026 09:42

-- Older SQL Server code uses CONVERT with a style number (101 is mm/dd/yyyy)
SELECT CONVERT(VARCHAR(10), CAST('2026-01-15' AS date), 101);
-- Output: 01/15/2026

When should you format dates?

Format a date when it has to be shown or grouped as text. Most uses fall into one of these four.

Show dates in a standard layout

Use ISO 8601 (YYYY-MM-DD) for files and APIs, MM/DD/YYYY for US readers and DD/MM/YYYY for most of Europe. Formatting also drops the time when you only want the date.

Group rows by month or year

Pull out just the part of the date you want, such as %Y for the year or %Y-%m for the month, and group by the result to get yearly or monthly totals.

Write dates people can read

Mix codes with words for labels, reminders and emails. “03/16 at 02:30 PM” reads better than “2026-03-16 14:30:00”.

Work with the day of the week

%w gives the day of the week as a number from 0 (Sunday) to 6 (Saturday). Filter on it to find weekdays or weekends, or map it to a name with CASE.

Real examples of date formatting

Stamp an export with ISO 8601 times

An API wants timestamps like 2026-03-15T08:05:12Z. The times in audit_log are stored in UTC, so put the T and Z around the codes (SQLite):

-- Input: audit_log
-- id | action | logged_at
-- ---+--------+--------------------
-- 1  | login  | 2026-03-15 08:05:12
-- 2  | export | 2026-03-15 14:30:45
-- 3  | logout | 2026-03-15 17:58:03

SELECT
  id,
  action,
  strftime('%Y-%m-%dT%H:%M:%SZ', logged_at) AS logged_at_iso
FROM audit_log;

-- Output:
-- id | action | logged_at_iso
-- ---+--------+---------------------
-- 1  | login  | 2026-03-15T08:05:12Z
-- 2  | export | 2026-03-15T14:30:45Z
-- 3  | logout | 2026-03-15T17:58:03Z

Count signups per month

Group users by the month they joined (PostgreSQL). YYYY-MM keeps the months in date order:

-- Input: users
-- id | name       | signup_date
-- ---+------------+------------
-- 1  | Ana Lima   | 2026-01-08
-- 2  | Ben Okafor | 2026-01-27
-- 3  | Chloe Park | 2026-02-14
-- 4  | Dev Mehta  | 2026-03-03
-- 5  | Elle Novak | 2026-03-30

SELECT
  TO_CHAR(signup_date, 'YYYY-MM') AS month,
  COUNT(*) AS signups
FROM users
GROUP BY TO_CHAR(signup_date, 'YYYY-MM')
ORDER BY month;

-- Output:
-- month   | signups
-- --------+--------
-- 2026-01 | 2
-- 2026-02 | 1
-- 2026-03 | 2

Write a booking confirmation

Spell out the day and month in a confirmation message (SQL Server):

-- Input: bookings
-- guest_name  | booking_time
-- ------------+--------------------
-- Priya Patel | 2026-03-16 14:30:00
-- Liam Murphy | 2026-03-18 09:15:00

SELECT
  guest_name,
  FORMAT(booking_time, 'dddd, MMMM d') + ' at ' + FORMAT(booking_time, 'h:mm tt') AS confirmation
FROM bookings;

-- Output:
-- guest_name  | confirmation
-- ------------+-------------------------------
-- Priya Patel | Monday, March 16 at 2:30 PM
-- Liam Murphy | Wednesday, March 18 at 9:15 AM

Find the busiest day of the week

Count orders by the name of the day they were placed on (MySQL):

-- Input: orders
-- order_id | order_date
-- ---------+--------------------
-- 1        | 2026-03-07 10:12:00
-- 2        | 2026-03-09 16:40:00
-- 3        | 2026-03-14 11:05:00
-- 4        | 2026-03-16 09:20:00
-- 5        | 2026-03-21 13:45:00

SELECT
  DATE_FORMAT(order_date, '%W') AS weekday,
  COUNT(*) AS order_count
FROM orders
GROUP BY weekday
ORDER BY order_count DESC;

-- Output:
-- weekday  | order_count
-- ---------+------------
-- Saturday | 3
-- Monday   | 2