How to Get First and Last Day of a Month in SQL Server

Tutorialsrack 29/04/2019 SQL SQL Server

In this article, we will learn how to get First day and Last Day of a current month, previous month and the last month in SQL Server.

To get the First Day of the current Month in SQL Server, a statement is as follow:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) as 'First Day of Current Month';

To get the Last Day of the current Month in SQL Server, a statement is as follow:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)) as 'Last Day of Current Month';

To get the First Day of the Previous Month in SQL Server, a statement is as follow:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) as 'First Day of Previous Month';

To get the Last Day of the Previous Month in SQL Server, a statement is as follow:

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE()) as 'Last Day of Previous Month';

To get the First Day of the Next Month in SQL Server, a statement is as follow:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0) as 'First Day of Next Month';

To get the Last Day of the Next Month in SQL Server, a statement is as follow:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))  as 'Last Day of Next Month';

I hope this article will help you to understand how to get the first day and last day of the selected month, previous month and next month in SQL Server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts