Sql Query to Get First, Last Date and Total number of Days in Month

Introduction: In this article I am going to share how to find first and last day of the month and count the total number of days in the month from the current or any date using 3 different queries.

In previous articles i explained how to Difference between temporary table and table variable in sql server

Description:  Many times we need to get the first and last day of the month and total number of days in the month for calculation or many other purposes. Here I have mentioned three ways to get the starting and end date of the month and count of total days in that month.

Implementation: Let’s write sample queries for demonstration purpose:

First Way:

DECLARE @MyDate DATE=GETDATE(), @FirstDate DATE,@LastDate DATE, @TotalDays INT

SET @TotalDays=(DATEDIFF(D,@FirstDate,@LastDate))+1

SELECT @FirstDate [First Date of Month] ,@LastDate [Last Date of Month], @TotalDays [Total Days in Month]

Second Way:

DECLARE @MyDate DATE=GETDATE(), @FirstDate DATE,@LastDate DATE, @TotalDays INT

SET @FirstDate=(SELECT DATEADD(mm, DATEDIFF(mm, 0, @MyDate ), 0))
SET @LastDate=(SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, @MyDate ) + 1, 0)))

SET @TotalDays=(DATEDIFF(D,@FirstDate,@LastDate))+1
SELECT @FirstDate [First Date of Month] ,@LastDate [Last Date of Month], @TotalDays [Total Days in Month]

Third Way:

DECLARE @MyDate DATE=GETDATE(), @FirstDate DATE,@LastDate DATE, @TotalDays INT

SET @FirstDate=(SELECT DATEADD(DAY ,-(DAY(@MyDate )-1),@MyDate ))
SET @TotalDays=(DATEDIFF(D,@FirstDate,@LastDate))+1

SELECT @FirstDate [First Date of Month] ,@LastDate [Last Date of Month], @TotalDays [Total Days in Month]

Note: I have used Current Date for demonstration purpose. If you want to get first and last date of the any date then replace your date with GETDATE() in above queries

Result:
 First Day of Month Last Day of Month Total Days in Month 2016-03-01 2016-03-31 31

Now over to you: