MYSQL SELECT WHERE MONTH AND YEAR

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:-

• MySQL Select Where Month and Year Syntax
• MySQL Select Where Month and Year Condition Examples
• Comparing Dates by Month and Year in MySQL Select
• MySQL Select Where Year Using Between
• How to get Month and Year in MySQL Select

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:-

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:-

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:-

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:-

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:-

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:-

READ MORE

• MySQL SELECT WHERE
• MySQL SELECT WHERE IN SubQuery
• MySQL SELECT WHERE LIKE
• MySQL SELECT WHERE NOT IN
• MySQL SELECT WHERE NULL

We hope this article helped you with MySQL Select Where queries relating to Month and Year. Good Luck!!!