Extract Month and Year from SQL DATE

sql

date-extraction

sql-functions

date-manipulation

byAnton Shumikhin·Dec 21, 2024

To extract Month and Year from date in SQL:

In SQL Server using FORMAT:

-- MAGIC_TIME: to turn back or forward we need a month and year, precisely
SELECT FORMAT(your_date_column, 'MM-yyyy') AS MonthYear 
FROM your_table;

For MySQL with DATE_FORMAT:

-- The moment when you finally remember the syntax!
SELECT DATE_FORMAT(your_date_column, '%m-%Y') AS MonthYear 
FROM your_table;

In PostgreSQL or Oracle using EXTRACT:

-- Because, honestly, who remembers entire dates?
SELECT EXTRACT(MONTH FROM your_date_column) AS Month,
       EXTRACT(YEAR FROM your_date_column) AS Year
FROM your_table;

This swiftly gives you just the Month-Year, sans day details.

Extracting with finesse

The standard answers serve well, but sometimes SQL feels like an art! Let’s get our hands a little messier with more refined techniques.

DATEADD/DATEDIFF approach

DATEADD and DATEDIFF in tandem, refocus onto the month and year:

-- Laser focus: Year and Month only!
SELECT DATEADD(month, DATEDIFF(month, 0, your_date_column), 0) AS MonthYear 
FROM your_table;

Good ol’ function extraction

Straightforward year and month extraction from SQL Server:

-- Year and Month: getting together since forever!
SELECT YEAR(your_date_column) AS Year, 
       MONTH(your_date_column) AS Month 
FROM your_table;

And if month names ring your bells, use DATENAME:

-- Because, Jan sounds cooler than 01!
SELECT DATENAME(month, your_date_column) AS MonthName
FROM your_table;

Precision with DATEPART

For compartmentalizing date:

-- An apple (year and month) a day, keeps confusion away!
SELECT DATEPART(yy, your_date_column) AS Year, 
       DATEPART(mm, your_date_column) AS Month 
FROM your_table;

Format considerations

Sometimes, presentability is key:

-- Because, who cares about the day when you have 'MM-yyyy'
SELECT CONVERT(VARCHAR(7), your_date_column, 126) AS MonthYearHyphen
FROM your_table;

Niche scenarios and format tweaks

Corner cases and variances keep things spicy in the SQL world!

Local folklore

Be wary of locale-dependant month names when using FORMAT or DATENAME().

Backwards compatibility

In SQL Server elder versions (pre-2012), use CONVERT or DATEPART() as FORMAT is absent.

Balancing speed with readability

DATEPART() comes handy when performance trumps readability due it being more index-friendly.

Grappling with complexity

Advanced formatting brings out the artist in SQL coders!

Grouping with aggregates

Imagine grouping sales per month:

-- TotalSales in MM-yyyy: Who said finance isn't fun?
SELECT FORMAT(SUM(sales), 'N2') AS TotalSales,
       FORMAT(sales_date, 'MM-yyyy') AS MonthYearGroup
FROM sales_table
GROUP BY FORMAT(sales_date, 'MM-yyyy');

Contemplation for groups

Using FORMAT in a GROUP BY: watch out for unexpected results due to locale settings or inefficient index utilisation.

Serialization and concatenation

Sometimes, readability is king:

-- A little verbosity never hurt anyone!
SELECT 'The month of ' + DATENAME(mm, your_date_column) + ', ' + CAST(YEAR(your_date_column) AS VARCHAR(4)) AS VerboseMonthYear
FROM your_table;