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 the user-defined function?

score 190 · Answer 1 · edited Dec 11 '17 at 04:59

190

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 the month.

DECLARE @ADate DATETIME

SET @ADate = GETDATE()

SELECT DAY(EOMONTH(@ADate)) AS DaysInMonth

edited Dec 11 '17 at 04:59

Kols

3,641
2
34
42

answered Feb 02 '13 at 09:40

Mikael Eriksson

136,425
22
210
281

1

And if you need the number of days in a given, you could also use the DATEFROMPARTS method. The code above would become SELECT DAY(EOMONTH(DATEFROMPARTS(@year, @month, 1))) AS DaysInMonth – nmariot Oct 13 '17 at 15:20
The beauty of this solution comes into play if @ADate is a complex calculated value ! +1 – Stefan Steiger Aug 03 '18 at 15:35
Thanks you so much! – Rejwanul Reja Mar 12 '20 at 10:14
This should be the actual answer to this question. – mpalmer78 Jun 18 '21 at 15:56

Mehrdad Afshari · Accepted Answer · 2015-02-05T22:08:01.720

123

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 every date:

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

edited Feb 05 '15 at 22:08

answered Mar 27 '09 at 19:00

Mehrdad Afshari

414,610
91
852
789

2

Like Stan says this will give inaccurate results in some cases – DJ. Mar 27 '09 at 19:12
But not for any given date as the OP wanted... ie 1/31/2009 returns 28 – DJ. Mar 27 '09 at 19:15
True, but it's very easy to workaround by adding `DAY(@date)-1` to the date. – Mehrdad Afshari Mar 27 '09 at 19:19
3

don't you mean : datediff ( day , dateadd ( day , 1-day(@date) , @date) , dateadd ( month , 1 , dateadd ( day , 1-day(@date) , @date))) – feihtthief Mar 27 '09 at 19:54
4

It's a rare corner case, but I just stumbled into it: This will throw an error for December 9999. – Heinzi Dec 17 '12 at 09:55
1

This doesn't work for any date in Dec 9999. You get overflow on the date type. This worked for me in SQL Server 2014: `case when datediff(m, dateadd(day, 1-day(@date), @date), convert(date, convert(datetime, 2958463))) > 0 then datediff(day, dateadd(day, 1-day(@date), @date), dateadd(month, 1, dateadd(day, 1-day(@date), @date))) else 31 end` – bradwilder31415 Jun 16 '16 at 20:27
5

All of the solutions mentioned here pale in comparison to the elegance of [@Mikael Eriksson's answer](https://stackoverflow.com/a/14660557/2738164). That works whenever a valid date is provided without crazy workarounds for niche cases and is far simpler code - I'd highly recommend anyone on T-SQL stick to getting the `day` component of the `eomonth` output. – bsplosion May 10 '19 at 18:15

Daniel Davis · Answer 3 · 2013-10-28T17:21:17.433

Most elegant solution: works for any @DATE

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

Throw it in a function or just use it inline. This answers the original question without all the extra junk in the other answers.

examples for 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

score 12 · Answer 4 · edited Jun 06 '13 at 20:35

--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 though, I would make a UDF for it if there is not a built in function...

score 8 · Answer 5 · edited Aug 08 '18 at 09:21

8

I would suggest:

SELECT DAY(EOMONTH(GETDATE()))

edited Aug 08 '18 at 09:21

ashleedawg

20,365
9
72
105

answered Apr 05 '17 at 07:07

gvschijndel

89
1
2

datepart doesn't return number of days and your answer is also wrong – TheGameiswar Apr 05 '17 at 07:10
Worked for me, but sure using the day function would be a more clean answer – gvschijndel Apr 05 '17 at 07:21
2

this answer is almost identical to the one below with votes of 75. – Hila DG Apr 23 '17 at 23:56
This is also the correct answer as `EOMONTH(GETDATE())` will return the end of the month date and the `DAY` function will return the day part from that month (in case of last day of month, it will be number of days in the month) – Khizar Iqbal Dec 10 '22 at 09:30

score 3 · Answer 6 · edited Apr 28 '16 at 11:15

3

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

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

Change getdate() to the date you need to count days for.

edited Apr 28 '16 at 11:15

gofr1

15,741
11
42
52

answered Apr 28 '16 at 10:55

Prashant

31
1

score 2 · Answer 7 · answered Mar 22 '19 at 12:54

2

   --- 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()))

answered Mar 22 '19 at 12:54

Saikh Rakif

135
3

score 1 · Answer 8 · answered Aug 21 '12 at 19:34

Solution 1: Find the number of days in whatever month we're currently in

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 a given month-year combo

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]

score 1 · Answer 9 · answered Apr 10 '13 at 08:39

1

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 creating any functions Work Fine

answered Apr 10 '13 at 08:39

pradeep

155
1
4
14

score 1 · Answer 10 · answered Feb 18 '15 at 12:31

You need to create a function, but it is for your own convenience. It works perfect and I never encountered any faulty computations 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 date's day number from the date itself gives you the last day of previous month. So, you need to add one month to the given date, subtract the day number and get the day component of the result.

score 1 · Answer 11 · answered Apr 12 '19 at 06:42

1

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

answered Apr 12 '19 at 06:42

kritika

21
1

score 1 · Answer 12 · edited Feb 02 '13 at 09:14

1

You do need to add a function, but it's a simple one. 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

edited Feb 02 '13 at 09:14

Himanshu

31,810
31
111
133

answered Mar 27 '09 at 19:01

2

The combination of DATEDIFF and DATEADD, by the way, doesn't always work. If you put a date of 1/31/2009 into it, the DATEADD will return 2/28/2009 and the DATEDIFF gives you 28, rather than 31. – Mar 27 '09 at 19:05
how to check, i mean what to execute to check the days in a month ?? – Nad Dec 24 '15 at 07:46

score 1 · Answer 13 · answered Jan 30 '23 at 12:47

1

use SQL Server EOMONTH Function nested with day to get last day of month

select Day(EOMONTH('2020-02-1')) -- Leap Year returns 29
select Day(EOMONTH('2021-02-1')) -- returns 28
select Day(EOMONTH('2021-03-1')) -- returns 31

answered Jan 30 '23 at 12:47

Mansoor Ali

11
2

score 1 · Answer 14 · edited Feb 02 '13 at 09:15

1

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]

edited Feb 02 '13 at 09:15

Himanshu

31,810
31
111
133

answered Mar 18 '12 at 14:34

Vignesh Anandaraj

11
1

score 0 · Answer 15 · edited Jul 13 '12 at 13:34

0

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

Nice 'n' Simple and does not require creating any functions

edited Jul 13 '12 at 13:34

Kijewski

25,517
12
101
143

answered Jul 13 '12 at 13:22

Paul

1

This is for SQL Server; I've never heard of a `subdate` function. – LittleBobbyTables - Au Revoir Oct 11 '12 at 14:29

score 0 · Answer 16 · answered Jul 14 '16 at 11:53

Mehrdad Afshari reply is most accurate one, apart from usual this answer is based on formal mathematical approach given by Curtis McEnroe in 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

VSV AdityaSarma · Answer 17 · 2016-12-08T08:22:41.910

To get the no. of days in 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 the result are 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 SQL Server SSMS

  (no column name)
1 31

Process:

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

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

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

or

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

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

Find No. of Days in a Month in SQL

ardem · Answer 18 · 2018-08-08T13:39:48.707

0

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

edited Aug 08 '18 at 13:39

answered Sep 10 '17 at 10:11

ardem

21
5

this doesn't return the number of days in the month – ashleedawg Aug 08 '18 at 09:22
Yes it does. But only for SQL Server 2012 and above – Alberto Cláudio Mandlate May 21 '19 at 10:02

score 0 · Answer 19 · edited Aug 29 '19 at 01:28

0

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]

edited Aug 29 '19 at 01:28

shizhen

12,251
9
52
88

answered Aug 29 '19 at 00:59

Danyil Vlasov

1

Milan Hettner · Answer 20 · 2019-10-24T10:26:40.860

0

RETURN day(dateadd(month, 12 * @year + @month - 22800, -1)) 
select day(dateadd(month, 12 * year(date) + month(date) - 22800, -1))

edited Oct 24 '19 at 10:26

answered Oct 24 '19 at 10:10

Milan Hettner

97
3

score 0 · Answer 21 · answered Jun 08 '21 at 09:26

A cleaner way of implementing this is using the datefromparts function to construct the first day of the month, and calculate the days from there.

CREATE FUNCTION [dbo].[fn_DaysInMonth]
(
    @year INT,
    @month INT
)
RETURNS INT
AS
BEGIN

IF @month < 1 OR @month > 12 RETURN NULL;
IF @year < 1753 OR @year > 9998 RETURN NULL;

DECLARE @firstDay DATE = datefromparts(@year, @month, 1);
DECLARE @lastDay DATE = dateadd(month, 1, @firstDay);

RETURN datediff(day, @firstDay, @lastDay);

END
GO

Similarily, you can calculate the days in a year:

CREATE FUNCTION [dbo].[fn_DaysInYear]
(
    @year INT
)
RETURNS INT
AS
BEGIN

IF @year < 1753 OR @year > 9998 RETURN NULL;

DECLARE @firstDay DATE = datefromparts(@year, 1, 1);
DECLARE @lastDay DATE = dateadd(year, 1, @firstDay);

RETURN datediff(day, @firstDay, @lastDay);

END
GO

score 0 · Answer 22 · answered Mar 27 '09 at 19:33

I upvoted Mehrdad, but this works as well. :)

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

To Test

declare @testDT datetime;

set @testDT = '2404-feb-15';

select dbo.GetDaysInMonth(@testDT)

score 0 · Answer 23 · answered Mar 27 '09 at 19:53

0

here's another one...

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

answered Mar 27 '09 at 19:53

Charles Bretana

143,358
22
150
216

score 0 · Answer 24 · answered Dec 23 '11 at 04:12

I know this question is old but I thought I would share what I'm using.

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

Those could be combine to create a single function to retrieve the number of days in a month if needed.

score -1 · Answer 25 · answered May 21 '13 at 14:09

-1

For any date

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

answered May 21 '13 at 14:09

Sujith

1

score -1 · Answer 26 · answered Jan 09 '17 at 05:17

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+datediff(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 no of months in that particular date

score -1 · Answer 27 · answered Jan 31 '18 at 11:20

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

Hi and welcome to SO! Although the code may speak for itself providing some details would help improve the quality of your answer! — mrun, Jan 31 '18 at 11:50

score -2 · Answer 28 · edited Jan 05 '15 at 12:47

-2

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)))

edited Jan 05 '15 at 12:47

Rahul Nikate

6,192
5
42
54

answered Jan 05 '15 at 11:42

ATUL KALASKAR

1

score -2 · Answer 29 · answered Apr 18 '16 at 08:10

-2

simple query in SQLServer2012 :

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

i tested for many dates and it return always a correct result

answered Apr 18 '16 at 08:10

zied zelfani

1

2

SELECT DAY(CAST('1951-05-20' AS DATE)) returns 20 which is the day portion of the date. It does not return the number of days in the month of May. – Even Mien Apr 19 '16 at 14:45

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

29 Answers29

Linked

Related