CODEWITHSUNDEEP
sqlsql-server
Sorath
Sorath

Reputation: 553

How do I get the number of days in a month for the specific date in that row of data using SQL?

For example, if I have a data set including two columns, one which shows the month as a number and the other which shows the year (result of grouping my data using GROUP BY), I want to add another column called 'Days in the month' which will display the number of days in the respective month. Is there a way I can do this? Is there some function I can add in the SELECT clause?

I want to do this since there are further calculations I need to do with that number for each row.

Upvotes: 1

Views: 167

Answers (2)

jdg
jdg

Reputation: 547

For older SQL Server versions, I use the following: 

DAY(DATEADD(MONTH, DATEDIFF(MONTH, -1, date_column)- 1, -1))

Much less elegant than the previous answer, but functional.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271051

In SQL Server 2012+, you can use:

select day(eomonth(datecol))

eomonth() gets the last day of the month. day() just returns the day of the month -- the number of days in the month, in this case.

Upvotes: 5

Related Questions