Open In App

SQL Query to get first and last day of a month in a Database

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we will discuss the overview of SQL query to perform an operation on database and our main focus will be on how to get the First and Last Day of a Month in a Database in SQL. Let’s discuss it one by one.

Introduction :
Queries in SQL help us to interact with the database to get done with the database creation, updating, retrieval, and manipulation to the user of the database. Here we talk about the SQL query to find the first and last day of the month in MySQL.In My SQL the date format is YYYY-MM-DD.

Example – 

2021-03-23
Monday Wednesday are first and last days of the month.
Example2:
2021-04-06
Thursday Friday are first and last days of the month.

Step-1: Creating a database –

CREATE DATABASE calendar;

Output :

Step-2: Using Database –
Using the database calendar as follows.

USE calendar;

Output :

Step-3: Creating a table in the calendar database –
Creating a table in the calendar database with table name as dates and column as day date as follows.

CREATE TABLE dates(daydate DATE);

Output :

Step-4: Verifying data into table dates –
To view the description of the table dates used the following SQL query as follows.

DESCRIBE dates;

Output :

Step-5: Inserting data into the table –
Inserting rows into the table as follows.

 INSERT INTO dates values('2021-03-23');
 INSERT INTO dates values('2021-04-06');
 INSERT INTO dates values('2022-03-23');
 INSERT INTO dates values('2030-08-26');
 INSERT INTO dates values('2035-09-23');

Output :

Step-6: Verifying the data –
To view the table with inserted data using the following SQL query as follows.

 SELECT* FROM dates;

Output :

Query to find out the first and last day of the date :
In MySQL, there is no direct function to find out the first day but there is a function for finding the day of the month of that particular date so with the help of this we can subtract the previous day date from the given day date to get the first day of the month.

Syntax :

Firstday: 
DATE_SUB(date1,INTERVAL DAYOFMONTH(date1)-1);
Syntax for DATE_SUB:
DATE_SUB(date1,date2) subtracts date2 from date1

SQL Query for last day –
For the last day, we can directly get by using function LAST_DAY.

Syntax :

LastDay:LAST_DAY(date);

Here in the above syntax DAYOFMONTH function gives the particular day as follows.

SELECT daydate, DATE_SUB(daydate,INTERVAL DAYOFMONTH(daydate)- 1 DAY) 
AS firstday,
LAST_DAY(daydate) 
AS lastday
FROM dates;

Output :
Here in the table by using AS the default column-header was changed.

Query to find out the first and last day of the date with weekdays : 
By using DAYNAME function we can get the weekday of the date as follows.

Syntax :

DAYNAME(date1);

In this table, both the weekdays of the first and last days of the month were also added as follows.

SELECT daydate, DATE_SUB(daydate,INTERVAL DAYOFMONTH(daydate)- 1 DAY) 
AS firstday,LAST_DAY(daydate) AS lastday,
DAYNAME( DATE_SUB(daydate,INTERVAL DAYOFMONTH(daydate)- 1 DAY)) 
AS weekday_of_fday ,DAYNAME(LAST_DAY(daydate)) 
AS weekday_of_lday
FROM dates;

Output :


Last Updated : 01 Dec, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads