Command guide

beginner date-time MySQL MariaDB SQL Server PostgreSQL SQLite

Extract Date Parts From A Date Or Timestamp

Return components such as year, month, and day from a date or timestamp value.

Created Apr 30, 2026 Last updated Apr 30, 2026 0/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Return year, month, and day columns for each event

The query turns each date into three independent numeric components. This is useful for reporting columns, simple calendar filters, or grouping by a date component without formatting the full date as text.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Launch', '2024-03-15'),
  (2, 'Review', '2025-11-02');

Validated query

SQL

SELECT
  name,
  EXTRACT(
    YEAR
    FROM
      event_date
  ) AS event_year,
  EXTRACT(
    MONTH
    FROM
      event_date
  ) AS event_month,
  EXTRACT(
    DAY
    FROM
      event_date
  ) AS event_day
FROM
  events
ORDER BY
  id;

Expected result

name	event_year	event_month	event_day
Launch	2024	3	15
Review	2025	11	2

SQL Server

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Launch', '2024-03-15'),
  (2, 'Review', '2025-11-02');

Validated query

SQL

SELECT
  name,
  YEAR (event_date) AS event_year,
  MONTH (event_date) AS event_month,
  DAY (event_date) AS event_day
FROM
  events
ORDER BY
  id;

Expected result

name	event_year	event_month	event_day
Launch	2024	3	15
Review	2025	11	2

PostgreSQL

Engine-specific syntax

Source table data

Setup

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

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Launch', '2024-03-15'),
  (2, 'Review', '2025-11-02');

Validated query

SQL

SELECT name, EXTRACT(YEAR FROM event_date)::INT AS event_year, EXTRACT(MONTH FROM event_date)::INT AS event_month, EXTRACT(DAY FROM event_date)::INT AS event_day FROM events ORDER BY id;

Expected result

name	event_year	event_month	event_day
Launch	2024	3	15
Review	2025	11	2

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE events (id INT, name TEXT, event_date TEXT);

INSERT INTO
  events (id, name, event_date)
VALUES
  (1, 'Launch', '2024-03-15'),
  (2, 'Review', '2025-11-02');

Validated query

SQL

SELECT
  name,
  CAST(strftime ('%Y', event_date) AS INT) AS event_year,
  CAST(strftime ('%m', event_date) AS INT) AS event_month,
  CAST(strftime ('%d', event_date) AS INT) AS event_day
FROM
  events
ORDER BY
  id;

Expected result

name	event_year	event_month	event_day
Launch	2024	3	15
Review	2025	11	2

MySQL, MariaDB, and PostgreSQL use EXTRACT. SQL Server uses dedicated date-part functions. SQLite uses strftime and casts the text result to integers.

Useful when

Where this command helps.

building reports grouped by calendar fields
filtering or displaying only one component of a timestamp

Explanation

What the command is doing.

Date-part extraction pulls one component out of a date or timestamp so it can be displayed, filtered, grouped, or joined. MySQL, MariaDB, and PostgreSQL support EXTRACT(part FROM value). SQL Server uses functions such as YEAR, MONTH, and DAY. SQLite stores dates as text or numbers and uses strftime format codes, commonly cast back to integers for numeric output.

Extract Date Parts From A Date Or Timestamp

Return year, month, and day columns for each event

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.