In this article, we will learn about how to get the total number of days in a month in SQL Server.
To get the total number of days in the previous month in a SQL Server, the query is as follows:
--To get the Total No. of Days in the Previous Month using Current Date( GETDATE() Function)
SELECT DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) as 'Total No. of Days';
Total No. of Days
------------------------
31
(1 row(s) affected)
To get the total number of days in the current month in a SQL Server, the query is as follows:
--To get the Total No. of Days in the Current Month using Current Date( GETDATE() Function)
SELECT DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))) as 'Total No. of Days';
Total No. of Days
------------------------
31
(1 row(s) affected)
To get the total number of days in the next month in a SQL Server, the query is as follows:
--To get the Total No. of Days in the Next Month using Current Date( GETDATE() Function)
SELECT DAY(DATEADD(DD,-1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))) as 'Total No. of Days';
Total No. of Days
-----------------------
29
(1 row(s) affected)
To get the total number of days in a month using any given date in SQL Server, the query is as follows:
--To get the Total No. of Days in a Month Using Any Given Date.
DECLARE @DATE DATETIME = '2020-02-10';
SELECT DAY(DATEADD(ms,-2,DATEADD(MONTH, DATEDIFF(MONTH,0,@DATE)+1,0))) AS 'Total No. of Days';
Total No. of Days
-----------------------
29
(1 row(s) affected)
In SQL Server 2012 and later version, we can get the total number of days in a month using EOMONTH() function. This method is the easiest way to get the total number of days in a month.
--To get the Total No. of Days in a Month Using EOMONTH() Function.
DECLARE @DATE DATETIME = '2020-02-10';
Select DAY(EOMONTH(@DATE)) AS 'Total No. of Days';Total No. of Days
-----------------------
29
(1 row(s) affected)
I hope this article will help you to understand how to get the total number of days in a month in SQL Server.
Share your valuable feedback, please post your comment at the bottom of this article. Thank you!
Comments