Command guide

intermediate date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Format A Date As A String

Convert a date value to a formatted string using a custom pattern such as MM/DD/YYYY.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Display event dates in MM/DD/YYYY format

The ISO date 2024-01-05 becomes 01/05/2024 after formatting. Each engine uses a different function name and token style but produces identical results. When switching engines, the format string must be translated: MySQL's %m/%d/%Y becomes PostgreSQL's MM/DD/YYYY and SQL Server's MM/dd/yyyy.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-01-05'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  DATE_FORMAT (event_date, '%m/%d/%Y') AS formatted_date
FROM
  events
ORDER BY
  id;

Expected result

name	formatted_date
Kickoff	01/05/2024
Review	03/15/2024

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-01-05'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  FORMAT (CAST(event_date AS DATE), 'MM/dd/yyyy') AS formatted_date
FROM
  events
ORDER BY
  id;

Expected result

name	formatted_date
Kickoff	01/05/2024
Review	03/15/2024

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-01-05'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT name, TO_CHAR(event_date::DATE, 'MM/DD/YYYY') AS formatted_date FROM events ORDER BY id;

Expected result

name	formatted_date
Kickoff	01/05/2024
Review	03/15/2024

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name VARCHAR(50), event_date VARCHAR(10));

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Kickoff', '2024-01-05'),
  (2, 'Review', '2024-03-15');

Validated query

SQL

SELECT
  name,
  STRFTIME ('%m/%d/%Y', event_date) AS formatted_date
FROM
  events
ORDER BY
  id;

Expected result

name	formatted_date
Kickoff	01/05/2024
Review	03/15/2024

MySQL/MariaDB use DATE_FORMAT with % tokens. PostgreSQL uses TO_CHAR with uppercase tokens. SQL Server uses FORMAT with .NET patterns. SQLite uses STRFTIME. All produce the same output.

Useful when

Where this command helps.

displaying dates in a locale-specific or report-friendly format
converting ISO dates to a more readable presentation

Explanation

What the command is doing.

Formatting a date as a string lets you control how it appears in query output or reports. MySQL and MariaDB use DATE_FORMAT(date, format) with %m, %d, %Y style tokens. PostgreSQL uses TO_CHAR(date, format) with MM, DD, YYYY tokens. SQL Server uses FORMAT(date, format) with .NET-style MM/dd/yyyy patterns — note that FORMAT can be slow on large result sets; CONVERT with a style code is faster for standard formats. SQLite uses STRFTIME(format, date) with the same %m, %d, %Y tokens as MySQL.

Format A Date As A String

Display event dates in MM/DD/YYYY format

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.