SQL YEAR_MONTH

What is SQL YEAR_MONTH and how is it used?

YEAR_MONTH is an interval qualifier or date-part specifier that represents a combined year and month value in SQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL YEAR_MONTH:

SQL YEAR_MONTH Full Explanation

YEAR_MONTH serves two closely related purposes in SQL.1. Interval qualifier – In interval literals and arithmetic, YEAR_MONTH tells the database that the literal contains both year and month fields. For example, INTERVAL '1-3' YEAR_MONTH represents one year and three months.2. Date-part specifier – In functions such as EXTRACT or DATE_TRUNC, YEAR_MONTH designates that the resulting numeric value should include the four-digit year followed by the two-digit month (YYYYMM).Support originates from the SQL-92 standard, and most enterprise-grade systems have implemented it. Behaviour is consistent: arithmetic involving YEAR_MONTH adjusts the year first and then the month, while EXTRACT(YEAR_MONTH FROM dt) returns an integer without separators (e.g., 202405). Caveats: some engines require a string literal, others allow ISO8601 style (e.g., INTERVAL 'P1Y2M'). Not all drivers can bind YEAR_MONTH intervals, so client-side formatting may be necessary.

SQL YEAR_MONTH Syntax

-- Interval literal
INTERVAL 'years-months' YEAR_MONTH;

-- Extracting a combined value
EXTRACT(YEAR_MONTH FROM <date>)

-- Adding a YEAR_MONTH interval
<date> + INTERVAL '1-6' YEAR_MONTH

SQL YEAR_MONTH Parameters

Example Queries Using SQL YEAR_MONTH

-- 1. Extract year and month as a single integer
SELECT EXTRACT(YEAR_MONTH FROM '2024-05-12') AS ym;  
-- Returns 202405

-- 2. Add 1 year 3 months to a date column
SELECT order_date,
       order_date + INTERVAL '1-3' YEAR_MONTH AS shifted
FROM   orders;

-- 3. Filter rows in the same year-month as today
SELECT *
FROM   sales
WHERE  EXTRACT(YEAR_MONTH FROM sale_date) = EXTRACT(YEAR_MONTH FROM CURRENT_DATE);

-- 4. Subtract two dates and return the difference in months only
SELECT TIMESTAMPDIFF(MONTH, start_date, end_date) AS months_diff
FROM   projects;

Expected Output Using SQL YEAR_MONTH

EXTRACT query returns integer 202405.
Interval addition shifts each date forward by exactly 15 months.
Filter returns rows whose sale_date falls in the current calendar year and month.
Difference in months appears as an integer column months_diff.

Use Cases with SQL YEAR_MONTH

Store year-month codes for partitioning or fast grouping.
Perform business-logic shifts such as adding fiscal years plus months.
Compare whether two timestamps occur in the same calendar year and month.
Build rolling 12-month reports without day-level precision.

Common Mistakes with SQL YEAR_MONTH

Forgetting the hyphen in interval literals: use '1-3' not '13'.
Expecting EXTRACT(YEAR_MONTH) to return a date – it returns a number.
Supplying invalid month values (e.g., INTERVAL '0-13' YEAR_MONTH).
Using YEAR_MONTH in systems that only support separate YEAR and MONTH parts (e.g., older SQLite builds).

First Introduced In

SQL-92 (core), first implemented widely in Oracle8i and MySQL 4.0

Frequently Asked Questions

What is the format of a YEAR_MONTH interval literal?

Use a single quoted string with a hyphen between the year and month parts, for example '2-4' for two years and four months.

Can I store YEAR_MONTH in a single column?

Yes. Many schemas store it as an INTEGER (e.g., 202405) or CHAR(6). Use CHECK constraints or generated columns to keep values valid.

How does YEAR_MONTH handle leap years?

Leap-year logic is irrelevant because the interval contains only year and month precision. Day alignment occurs after the month shift is applied.

Is YEAR_MONTH available in SQLite?

Recent SQLite builds include limited support, but older versions require manual math using STRFTIME or substrings.