Please disable your adblock and script blockers to view this page
 

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. 
Previous
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily.. ConversionConversion EmoticonEmoticon