EXTRACT / FORMAT

-- Extracts a specified field from a date (standard SQL).
SELECT EXTRACT(field FROM date);

-- Converts a date to a formatted string (MySQL).
SELECT DATE_FORMAT(date, 'format_string');

-- Converts a date to a formatted string (PostgreSQL).
SELECT TO_CHAR(date, 'format_string');

Syntax	Description
EXTRACT(YEAR FROM date)	Extracts the year from a date as an integer.
EXTRACT(MONTH FROM date)	Extracts the month (1–12) from a date as an integer.
EXTRACT(DAY FROM date)	Extracts the day (1–31) from a date as an integer.
EXTRACT(HOUR FROM datetime)	Extracts the hour (0–23) from a datetime value as an integer.
DATE_FORMAT(date, format)	Converts a date to a formatted string in MySQL.
TO_CHAR(date, format)	Converts a date to a formatted string in PostgreSQL.

-- Extracts the year and month from the order date.
SELECT order_id,
       EXTRACT(YEAR FROM order_date)  AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

-- Counts the number of orders per month.
SELECT EXTRACT(YEAR FROM order_date)  AS year,
       EXTRACT(MONTH FROM order_date) AS month,
       COUNT(*) AS order_count
FROM orders
GROUP BY year, month
ORDER BY year, month;

-- Formats the date as "October 15, 2025" style (MySQL).
SELECT DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date
FROM orders;

-- Formats the date as "2025/10/15" style (PostgreSQL).
SELECT TO_CHAR(order_date, 'YYYY/MM/DD') AS formatted_date
FROM orders;

-- Example result of: SELECT order_id, EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month FROM orders;
-- +----------+------------+-------------+
-- | order_id | order_year | order_month |
-- +----------+------------+-------------+
-- |        1 |       2025 |           8 |
-- |        2 |       2025 |          10 |
-- |        3 |       2025 |          10 |
-- +----------+------------+-------------+

-- Example result of: SELECT DATE_FORMAT(order_date, '%Y年%m月%d日') AS formatted_date FROM orders;
-- +------------------+
-- | formatted_date   |
-- +------------------+
-- | 2025年08月01日   |
-- | 2025年10月05日   |
-- | 2025年10月15日   |
-- +------------------+

『EXTRACT』is standard SQL and works in both MySQL and PostgreSQL.

SELECT EXTRACT(YEAR FROM order_date) AS order_year,
       EXTRACT(MONTH FROM order_date) AS order_month
FROM orders;

Date formatting functions differ by database. In PostgreSQL, use 『TO_CHAR』.

-- Formats the date as "2025/10/15" style (PostgreSQL).
SELECT TO_CHAR(order_date, 'YYYY/MM/DD') AS formatted_date FROM orders;

In Oracle, both 『EXTRACT』and 『TO_CHAR』are available. The format specifiers are the same as in PostgreSQL.

-- Extracts the year and month (Oracle).
SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM orders;

-- Formats the date (Oracle).
SELECT TO_CHAR(order_date, 'YYYY"年"MM"月"DD"日"') AS formatted_date FROM orders;

In SQL Server, use the 『YEAR()』, 『MONTH()』, and 『DAY()』functions to extract fields, and 『FORMAT』to format dates.

-- Extracts the year and month (SQL Server).
SELECT YEAR(order_date) AS order_year, MONTH(order_date) AS order_month FROM orders;

-- Formats the date (SQL Server 2012 and later).
SELECT FORMAT(order_date, 'yyyy/MM/dd') AS formatted_date FROM orders;

In SQLite, use the 『strftime』function.

-- Extracts the year and month (SQLite).
SELECT strftime('%Y', order_date) AS order_year,
       strftime('%m', order_date) AS order_month
FROM orders;

『EXTRACT』is defined in standard SQL and works in both MySQL and PostgreSQL. Fields you can extract include YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), and WEEK (week number).

Common format specifiers for MySQL's 『DATE_FORMAT』include 『%Y』(4-digit year), 『%m』(2-digit month), 『%d』(2-digit day), 『%H』(hour in 24-hour format), 『%i』(minutes), and 『%s』(seconds). For PostgreSQL's 『TO_CHAR』, use 『YYYY』, 『MM』, 『DD』, 『HH24』, 『MI』, and 『SS』.

To get the current date or datetime, see 『CURRENT_DATE / NOW』. For date arithmetic, see 『DATE_ADD / DATEDIFF』.