How to determine the number of days in a month in SQL Server?

Question

I need to determine the number of days in a month for a given date in SQL Server.

Is there a built-in function? If not, what should I use as a custom function?

Answer 1

You can use the following with the first day of the specified month:

datediff(day, @date, dateadd(month, 1, @date)) 

To make it work for each date:

 datediff(day, dateadd(day, 1-day(@date), @date), dateadd(month, 1, dateadd(day, 1-day(@date), @date))) 

Answer 2

In SQL Server 2012, you can use EOMONTH (Transact-SQL) to get the last day of the month, and then you can use DAY (Transact-SQL) to get the number of days in a month.

 DECLARE @ADate DATETIME SET @ADate = GETDATE() SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth 

Answer 3

The most elegant solution: works for any @DATE

 DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,@DATE),0))) 

Throw it into a function or just use it in a string. This answers the original question without unnecessary junk in the other answers.

examples of dates from other answers:

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'1/31/2009'),0))) Returns 31

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2404-feb-15'),0))) Returns 29

SELECT DAY(DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,-1,'2011-12-22'),0))) Returns 31

Answer 4

Much easier ... try day(eomonth(@Date))

Answer 5

 --Last Day of Previous Month SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))) --Last Day of Current Month SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))) --Last Day of Next Month SELECT DATEPART(day, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))) 

Personally, however, I would do UDF for it if there is no built-in function ...

Answer 6

I would suggest:

 SELECT DAY(EOMONTH(GETDATE())) 

Answer 7

This code gets the number of days in the current month:

 SELECT datediff(dd,getdate(),dateadd(mm,1,getdate())) as datas 

Change getdate() to the date on which you want to count the days.

Answer 8

  --- sql server below 2012--- select day( dateadd(day,-1,dateadd(month, 1, convert(date,'2019-03-01')))) -- this for sql server 2012-- select day(EOMONTH(getdate())) 

Answer 9

You need to add a function, but it is simple. I use this:

 CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] ( @pDate DATETIME ) RETURNS INT AS BEGIN SET @pDate = CONVERT(VARCHAR(10), @pDate, 101) SET @pDate = @pDate - DAY(@pDate) + 1 RETURN DATEDIFF(DD, @pDate, DATEADD(MM, 1, @pDate)) END GO 

Answer 10

 SELECT Datediff(day, (Convert(DateTime,Convert(varchar(2),Month(getdate()))+'/01/'+Convert(varchar(4),Year(getdate())))), (Convert(DateTime,Convert(varchar(2),Month(getdate())+1)+'/01/'+Convert(varchar(4),Year(getdate()))))) as [No.of Days in a Month] 

Answer 11

Solution 1. Find the number of days in any month in which we are currently

 DECLARE @dt datetime SET @dt = getdate() SELECT @dt AS [DateTime], DAY(DATEADD(mm, DATEDIFF(mm, -1, @dt), -1)) AS [Days in Month] 

Solution 2. Find the number of days in this combined month of the month.

 DECLARE @y int, @m int SET @y = 2012 SET @m = 2 SELECT @y AS [Year], @m AS [Month], DATEDIFF(DAY, DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)), DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0)) ) AS [Days in Month] 

Answer 12

 select datediff(day, dateadd(day, 0, dateadd(month, ((2013 - 1900) * 12) + 3 - 1, 0)), dateadd(day, 0, dateadd(month, ((2013 - 1900) * 12) + 3, 0)) ) 

Nice Simple and does not require the creation of any Work Fine features

Answer 13

You need to create a function, but it is for your convenience. It works perfect, and I never came across any erroneous calculations using this function.

 CREATE FUNCTION [dbo].[get_days](@date datetime) RETURNS int AS BEGIN SET @date = DATEADD(MONTH, 1, @date) DECLARE @result int = (select DAY(DATEADD(DAY, -DAY(@date), @date))) RETURN @result END 

How it works: subtracting the number of days of a date from the date itself gives you the last day of the previous month. Thus, you need to add one month to the specified date, subtract the day number and get the day component of the result.

Answer 14

 select add_months(trunc(sysdate,'MM'),1) - trunc(sysdate,'MM') from dual; 

Answer 15

I supported Mehrdad, but it works too. :)

 CREATE function dbo.IsLeapYear ( @TestYear int ) RETURNS bit AS BEGIN declare @Result bit set @Result = cast( case when ((@TestYear % 4 = 0) and (@testYear % 100 != 0)) or (@TestYear % 400 = 0) then 1 else 0 end as bit ) return @Result END GO CREATE FUNCTION dbo.GetDaysInMonth ( @TestDT datetime ) RETURNS INT AS BEGIN DECLARE @Result int DECLARE @MonthNo int Set @MonthNo = datepart(m,@TestDT) Set @Result = case @MonthNo when 1 then 31 when 2 then case when dbo.IsLeapYear(datepart(yyyy,@TestDT)) = 0 then 28 else 29 end when 3 then 31 when 4 then 30 when 5 then 31 when 6 then 30 when 7 then 31 when 8 then 31 when 9 then 30 when 10 then 31 when 11 then 30 when 12 then 31 end RETURN @Result END GO 

Verify

 declare @testDT datetime; set @testDT = '2404-feb-15'; select dbo.GetDaysInMonth(@testDT) 

Answer 16

here is another one ...

 Select Day(DateAdd(day, -Day(DateAdd(month, 1, getdate())), DateAdd(month, 1, getdate()))) 

Answer 17

I know this question is old, but I thought I would share what I use.

 DECLARE @date date = '2011-12-22' /* FindFirstDayOfMonth - Find the first date of any month */ -- Replace the day part with -01 DECLARE @firstDayOfMonth date = CAST( CAST(YEAR(@date) AS varchar(4)) + '-' + CAST(MONTH(@date) AS varchar(2)) + '-01' AS date) SELECT @firstDayOfMonth 

and

 DECLARE @date date = '2011-12-22' /* FindLastDayOfMonth - Find what is the last day of a month - Leap year is handled by DATEADD */ -- Get the first day of next month and remove a day from it using DATEADD DECLARE @lastDayOfMonth date = CAST( DATEADD(dd, -1, DATEADD(mm, 1, FindFirstDayOfMonth(@date))) AS date) SELECT @lastDayOfMonth 

They can be combined to create one function to get the number of days in a month, if necessary.

Answer 18

 SELECT DAY(SUBDATE(ADDDATE(CONCAT(YEAR(NOW()), '-', MONTH(NOW()), '-1'), INTERVAL 1 MONTH), INTERVAL 1 DAY)) 

Nice 'n' Simple and does not require the creation of any functions

Answer 19

Mehrdad Afshari's answer is the most accurate, besides the usual answer, this question is based on a formal mathematical approach given by Curtis McEnroe on his blog https://cmcenroe.me/2014/12/05/days-in-month-formula.html

 DECLARE @date DATE= '2015-02-01' DECLARE @monthNumber TINYINT DECLARE @dayCount TINYINT SET @monthNumber = DATEPART(MONTH,@date ) SET @dayCount = 28 + (@monthNumber + floor(@monthNumber/8)) % 2 + 2 % @monthNumber + 2 * floor(1/@monthNumber) SELECT @dayCount + CASE WHEN @dayCount = 28 AND DATEPART(YEAR,@date)%4 =0 THEN 1 ELSE 0 END -- leap year adjustment 

Answer 20

To get a number. days a month, we can directly use Day (), available in SQL.

Follow the link posted at the end of my answer for SQL Server 2005/2008.

The following example and result from SQL 2012

 alter function dbo.[daysinm] ( @dates nvarchar(12) ) returns int as begin Declare @dates2 nvarchar(12) Declare @days int begin select @dates2 = (select DAY(EOMONTH(convert(datetime,@dates,103)))) set @days = convert(int,@dates2) end return @days end --select dbo.daysinm('08/12/2016') 

Result in SSMS SQL Server

  (no column name) 1 31 

process:

When EOMONTH is used, depending on the date format we use, it is converted to the SQL Server DateTime format. Then the EOMONTH () date output will be 2016-12-31, having 2016 as Year, 12 as Month and 31 as Days. This output, when passed to Day (), gives you the total number of days in a month.

If we want to get an instant result for verification, we can directly run the code below,

 select DAY(EOMONTH(convert(datetime,'08/12/2016',103))) 

or

 select DAY(EOMONTH(convert(datetime,getdate(),103))) 

for links to work in SQL Server 2005/2008/2012, please follow the following external link ...

Find the number of days per month in SQL

Answer 21

 DECLARE @m int SET @m = 2 SELECT @m AS [Month], DATEDIFF(DAY, DATEADD(DAY, 0, DATEADD(m, +@m -1, 0)), DATEADD(DAY, 0, DATEADD(m,+ @m, 0)) ) AS [Days in Month] 

Answer 22

For any date

 select DateDiff(Day,@date,DateAdd(month,1,@date)) 

Answer 23

 DECLARE @date nvarchar(20) SET @date ='2012-02-09 00:00:00' SELECT DATEDIFF(day,cast(replace(cast(YEAR(@date) as char)+'-'+cast(MONTH(@date) as char)+'-01',' ','')+' 00:00:00' as datetime),dateadd(month,1,cast(replace(cast(YEAR(@date) as char)+'-'+cast(MONTH(@date) as char)+'-01',' ','')+' 00:00:00' as datetime))) 

Answer 24

simple query in SQLServer2012:

select day (('20 -05-1951 22:00:00 '))

i tested for many dates and always returned the correct result

Answer 25

select first_day = dateadd (dd, -1 * datepart (dd, getdate ()) + 1, getdate ()), LAST_DAY = DateAdd (dd, -1 * DatePart (dd, DateAdd (mm, 1, GETDATE ())) , DateAdd (mm, 1, GETDATE ())), no_of_days = 1 + dateiff (dd, dateadd (dd, -1 * datepart (dd, getdate ()) + 1, getdate ()), dateadd (dd, -1 * datepart (dd, dateadd (mm, 1, getdate ())), DateAdd (mm, 1, GETDATE ())))

replace any date with getdate to get the number of months on that specific date

Answer 26

 DECLARE @date DATETIME = GETDATE(); --or '12/1/2018' (month/day/year) SELECT DAY(EOMONTH ( @date )) AS 'This Month'; SELECT DAY(EOMONTH ( @date, 1 )) AS 'Next Month'; 

result: this month 31

Next month 30

Answer 27

 DECLARE @Month INT=2, @Year INT=1989 DECLARE @date DateTime=null SET @date=CAST(CAST(@Year AS nvarchar) + '-' + CAST(@Month AS nvarchar) + '-' + '1' AS DATETIME); DECLARE @noofDays TINYINT DECLARE @CountForDate TINYINT SET @noofDays = DATEPART(MONTH,@date ) SET @CountForDate = 28 + (@noofDays + floor(@noofDays/8)) % 2 + 2 % @noofDays + 2 * floor(1/@noofDays) SET @noofDays= @CountForDate + CASE WHEN @CountForDate = 28 AND DATEPART(YEAR,@date)%4 =0 THEN 1 ELSE 0 END PRINT @noofDays