How to get number of days in a month in SQL Server

Question

Is there a way to get the number of days in a month in SQL Server, if we input the month number or month name, or even a date?

Possible duplicate of [How to determine the number of days in a month in SQL Server?](https://stackoverflow.com/questions/691022/how-to-determine-the-number-of-days-in-a-month-in-sql-server) — sniperd, Aug 09 '18 at 13:14

score 12 · Accepted Answer · answered Aug 09 '18 at 12:17

12

You can use:

select day(eomonth ('2018-02-01')) as NoOfDays

and the result will be:

NoOfDays
-----------
28

answered Aug 09 '18 at 12:17

AswinRajaram

1,519
7
18

score 10 · Answer 2 · answered Aug 09 '18 at 12:15

10

If you have a date, then simply do:

select day(eomonth(date))

If you have a month number, then:

select day(eomonth(datefromparts(2020, month_number, 1)))

answered Aug 09 '18 at 12:15

Gordon Linoff

1,242,037
58
646
786

@CodeMachine . . . You are the OP and can accept whichever answer you like. However, I'm curious why you unaccepted this answer for an equivalent answer posted several minutes later. – Gordon Linoff Aug 10 '18 at 10:27

Andrew · Answer 3 · 2018-08-09T12:22:47.077

5

If you have a date and are on 2012 or later :

SELECT day(eomonth(yourdate))

Month name / number is automatically prone to an error when dealing with February - do you consider it 28 or 29, which year are you referring to when making that calculation etc.

edited Aug 09 '18 at 12:22

answered Aug 09 '18 at 12:15

Andrew

26,629
5
63
86

2012+. This will break in versions 2008. – SQL_M Aug 09 '18 at 12:21

t-clausen.dk · Answer 4 · 2018-08-09T13:35:23.900

4

In case you are using sql-server 2008 or earlier:

Date as input

DECLARE @date DATETIME = getdate()

SELECT day(dateadd(m, datediff(m, -1, @date), -1))

Month and year as input

DECLARE @year  INT = 2018
DECLARE @month INT = 2

SELECT day(dateadd(m, @month + datediff(m, 0, cast(@year as char(4))), -1))

edited Aug 09 '18 at 13:35

answered Aug 09 '18 at 12:27

t-clausen.dk

43,517
12
59
92

score 3 · Answer 5 · answered Aug 09 '18 at 12:17

3

try using

SELECT day(eomonth(yourdate))

answered Aug 09 '18 at 12:17

Chanukya

5,833
1
22
36

score 2 · Answer 6 · answered Aug 09 '18 at 12:25

for pre 2012 where eomonth() is not available

if you have a date

select  day(dateadd(month, datediff(month, 0, @date) + 1, -1))

if you have the year & month

declare @year   int = 2018,
        @month  int = 8
select  dateadd(month, @month, dateadd(year, @year - 1900, -1))

How to get number of days in a month in SQL Server

6 Answers6