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');

The following examples use the orders table.

Extracts the year and month from the order date.

sample_extract_format.sql

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 |
+----------+------------+-------------+
3 rows in set

Counts the number of orders per month.

Note: Using aliases defined in SELECT (such as year and month) directly in GROUP BY is a MySQL extension. In PostgreSQL, you need to write GROUP BY EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date) instead.

sample_extract_format.sql

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;

+------+-------+-------------+
| year | month | order_count |
+------+-------+-------------+
| 2025 |     8 |           1 |
| 2025 |    10 |           2 |
+------+-------+-------------+
2 rows in set

Formats the date as "2025/10/15" style (MySQL).

sample_extract_format.sql

SELECT DATE_FORMAT(order_date, '%Y/%m/%d') AS formatted_date
FROM orders;

+----------------+
| formatted_date |
+----------------+
| 2025/08/01     |
| 2025/10/05     |
| 2025/10/15     |
+----------------+
3 rows in set

Formats the date as "2025/10/15" style (PostgreSQL).

sample_extract_format.sql

SELECT TO_CHAR(order_date, 'YYYY/MM/DD') AS formatted_date
FROM orders;

+----------------+
| formatted_date |
+----------------+
| 2025/08/01     |
| 2025/10/05     |
| 2025/10/15     |
+----------------+
3 rows in set

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.