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 Get day,month and year from current or any date and Separate integer and fractional part from decimal number and Insert multiple records in table in single insert statement and Using case expression in select statement in sql server and 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 @FirstDate=(SELECT DATEADD(dd,-DAY(@MyDate )+1,@MyDate ))

SET @LastDate=(SELECT DATEADD(dd,-DAY(DATEADD(mm,1,@MyDate )),DATEADD(mm,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]

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 @LastDate=(SELECT  DATEADD(DAY ,-(DAY(@MyDate )), DATEADD(MONTH,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:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates. 

• Tweet
• Share
• Share
• Share

Related Post