神刀安全网

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. 


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 two 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 @CurrentDate DATE = GETDATE (), @FirstDate DATE , @LastDate DATE , @TotalDays INT

SET @FirstDate =( SELECT DATEADD ( dd ,- DAY ( @CurrentDate )+ 1 , @CurrentDate ))

SET @LastDate =( SELECT DATEADD ( dd ,- DAY ( DATEADD ( mm , 1 , @CurrentDate )), DATEADD ( mm , 1 , @CurrentDate )))

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 @CurrentDate DATE = GETDATE (), @FirstDate DATE , @LastDate DATE , @TotalDays INT

SET @FirstDate =( SELECT DATEADD ( mm , DATEDIFF ( mm , 0 , @CurrentDate ), 0 ))

SET @LastDate =( SELECT DATEADD ( dd , 1 , DATEADD ( mm , DATEDIFF ( mm , 0 , @CurrentDate ) + 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]  

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.  

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Sql Query to Get First, Last Date and Total number of Days in Month

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
分享按钮