How to Get the Month from a Date in MySQL

Problem

You’d like to get the month from a date/datetime column in a MySQL database.

Example

Our database has a table named apartment_rental with data in the columns id, address, floor, and start_date.

For each available apartment, get the address, the floor, and the month when it’s available. Get only the month from the start_date column.

Solution

We’ll use the MONTH() function. Here’s the query you’d write:

SELECT 
  address,
  floor,
  MONTH(start_date) AS start_month
FROM apartment_rental;

Here’s the result of the query:

Discussion

Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column. (In our example, we use the start_date column of date data type).

MONTH() returns the month as an integer from 1 to 12 (1 is January and 12 is December). In our example, the apartment at 700 Oak Street will start renting in month 3 because its start date is 2019-03-20.