how do I get month from date in mysql

how do I get month from date in mysql

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

How to Extract Month from Date in MySQL

Filtering database records by a specific month and year based on user input is a common requirement for reporting dashboards and analytics. The immediate instinct when facing this problem is often to try and extract the month component directly within the WHERE clause using the MONTH() function. However, as seen in many early attempts, there is a critical distinction between extracting the month from a stored date column versus comparing that extracted value against a raw integer representing the month (e.g., 1 for January).

The error encountered when writing MONTH('$m') stems from a misunderstanding of how the function works and how PHP variables interact with SQL strings. The MONTH() function expects a date string or timestamp as its argument, not an integer variable containing just the number "5" (for May). If you pass an integer to MONTH(), MySQL throws an error because it cannot convert that integer into a valid date format to extract a month from.

Furthermore, comparing dateStart = MONTH('$m') is logically flawed for two reasons. First, dateStart is likely a full datetime or date string (e.g., '2023-10-15 14:30:00'), while MONTH() returns an integer between 1 and 12. You cannot equate a full date string to a single digit number. Second, you are comparing the raw input $m against the database column without accounting for the year or ensuring the data type compatibility.

To correctly filter results where startDate falls within a specific month of a given year, you must compare the extracted parts of the database column against the extracted parts of your user input, or better yet, leverage MySQL's date arithmetic functions to handle the range logic efficiently.

The most robust approach involves using DATE_FORMAT() on the startDate column to create strings that match exactly with formatted inputs from your application. If your PHP application provides $m as a string like "October" and $y as "2023", you can construct a query like this:

SELECT * 
FROM table 
WHERE amount > 1000 
  AND DATE_FORMAT(startDate, '%M %Y') = CONCAT('$m', ' ', '$y');

However, relying on string comparison for dates can be brittle if the data types don't align perfectly. A more performant and standard SQL approach is to extract the month and year directly from the startDate column and compare them as integers. This avoids the overhead of formatting functions during every query execution and works regardless of how the date is stored internally (whether it includes time or not).

SELECT * 
FROM table 
WHERE amount > 1000 
  AND MONTH(startDate) = MONTH_FORMAT('$m', '%M') -- Convert input to integer month
  AND YEAR(startDate) = CAST('$y' AS UNSIGNED);

In a Laravel application, the query builder handles this elegantly without manual string concatenation. You can pass raw expressions or use helper methods to ensure the MONTH() function is applied directly to the column:

$month = $this->getMonthNumber($m); // Helper to convert 'October' to 10
$year = (int)$y;

$query = DB::table('table')
    ->where('amount', '>', 1000)
    ->whereRaw("MONTH(start_date) = ?", [$month])
    ->whereRaw("YEAR(start_date) = ?", [$year]);

This method ensures that you are comparing integers to integers, which is faster and less prone to SQL injection risks than string concatenation. It also handles the logic of fetching records specifically for that month-year combination, ignoring days or times stored in the startDate column.

One significant pitfall to watch out for involves indexing. If you frequently query by date ranges or specific months, relying on MONTH(startDate) in a WHERE clause can prevent the use of standard B-tree indexes on the startDate column. This is because functions applied to columns obscure the actual data value stored. For high-traffic reporting tables, consider storing a separate month_start_date column (e.g., '2023-10-01') and indexing that. You can then filter using:

WHERE amount > 1000 AND startDate >= '2023-10-01' AND startDate < '2023-11-01'

This range-based query is index-friendly and often preferred in high-performance scenarios. For most standard applications, however, the MONTH() and YEAR() extraction method remains a clean, readable solution that directly answers how to get month from date in MySQL while maintaining data integrity.

Our network