In this article, we will be discussing MySQL SELECT WHERE statements with MONTH and YEAR conditions. We will be going through various examples to illustrate the same.

Table of Contents:-

Let us get started by making the sample data. We will be creating a table named student_enroll_date, followed by inserting some rows into it.

#create the table student_enroll_date;  
CREATE TABLE student_enroll_date (
student_id INT,
student_name VARCHAR(255),
enroll_date DATE
);
# insert rows into the table student_enroll_date;  
INSERT INTO student_enroll_date(student_id,student_name,enroll_date) 
VALUES(1,"Justin",'2019-12-20'),
(2,"Veronica",'2018-10-22'),
(3,"George",'2019-11-02'),
(4,"Peter",'2020-03-08'),
(5,"Smitha",'2019-04-05'),
(6,"Walter",'2019-03-06'),
(7,"Atharv",'2018-07-12'),
(8,"Avisha",'2019-10-06'),
(9,"Jennefir",'2019-12-26'),
(10,"Mounica",'2020-05-09'),
(11,"Walter",'2018-02-06'),
(12,"Peter",'2020-07-08');

Let us see what got inserted into the table student_enroll_date by executing:

SELECT * FROM student_enroll_date;

Output:-

image_1 : student_enroll_date

MySQL Select Where Month and Year Syntax

SELECT columnList/*  FROM tableName [ WHERE YEAR(columnName) <condition1> AND / OR  MONTH(columnName) <condition2> ] ;
  • columnList/*: List of the column values to be selected as part of the output.
  • tableName: Name of the table from where we will make the selection.
  • columnName: Name of the column which has the DATE datatype and is used in the comparison conditions.
  • YEAR(): This function will return year for a date (passed as parameter) within the range of 1000 to 9999 or 0 for the ‘zero’ date.
  • MONTH(): This function will return the month of date (passed as parameter) within the range of 1 to 12 or 0 for dates with a ‘zero’ month part.
  • condition1 and condition2: are the conditions used for year and month comparisons.

MySQL Select Where Month and Year Condition Examples

Example: Get the enrolment details of the students with the enrolment year 2019 and enrolment month December.

Observe the below query for the solution.

SELECT * FROM student_enroll_date WHERE YEAR(enroll_date) = 2019 AND MONTH(enroll_date) = 12;

Action Output Message:-

2 row(s) returned.

Output:-

image_2

The output in image_2 shows that two rows are returned, which had December 2019 in the enroll_date.

Comparing Dates by Month and Year in MySQL Select

Example1: Get the student enrolment details for the year after 2019.

Observe the below query for the solution.

SELECT * FROM student_enroll_date WHERE YEAR(enroll_date) > 2019 ;

Action Output Message:-

3 row(s) returned.

Output:-

image_3

The output in image_3 shows that three rows are returned, which had a year greater than 2019 in enroll_date.

Example2: Get the enrolment details of all the students who enrolled before July of any year.

Observe the below query for the solution.

SELECT * FROM student_enroll_date WHERE MONTH(enroll_date) <= 6;

Action Output Message:-

5 row(s) returned.

Output:-

image_4

MySQL Select Where Year Using Between

Example: Get the student enrolment details for the years between 2018 and 2019.

Observe the below query for the solution.

SELECT * FROM student_enroll_date WHERE YEAR(enroll_date) BETWEEN 2018 AND 2019 ;

Action Output Message:-

9 row(s) returned.

Output:-

image_5

How to get Month and Year in MySQL Select

Example: Get the Year and Month of enrolment for the student named Justin.

Observe the below query for the solution.

SELECT 
    YEAR(enroll_date) AS 'Enrollment Year',
    MONTH(enroll_date) AS 'Enrollment Month'
FROM
    student_enroll_date
WHERE
    student_name = 'Justin';

Action Output Message:-

1 row(s) returned.

Output:-

image_6

READ MORE

We hope this article helped you with MySQL Select Where queries relating to Month and Year. Good Luck!!! 
You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂