aspdotnet-suresh offers articles and tutorials,csharp dot net, articles and tutorials,VB.NET Articles,Gridview articles,code examples of 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

SQL Server - How to Find Total Number of Days in a Month

Apr 9, 2013

Here I will explain how to get or find total number of days in month using SQL Server.

In previous articles I explained Insert values in identity column in SQL Server, Check if string contains specific word in SQL, SQL Query to read xml file,
SQL Query to get duplicate records count, SQL Query to get month wise, year wise data and many articles relating to SQL Server, jQuery, JavaScript. Now I will explain how to get or find total number of days in month using SQL Server.

To get total number of days in a month we need to write the query like as shown below

DECLARE @sample datetime
SET @sample=GETDATE()  -- Sample date
select datediff(dd,dateadd(dd, 1-day(@sample),@sample), dateadd(m,1,dateadd(dd, 1-day(@sample),@sample)))
By using above query we can get total number of days for particular month.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest,, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email


Anonymous said...

hah/...Good One.. :)

Anonymous said...

It is not working as expected.When I run this query I'm getting output:28 where expected result:30.As the current month is APRIL and it has 30days.
Hope I'm not wrong.

Anonymous said...

query not working properly

Shefin said...

Try this:
DECLARE @sample datetime
SET @sample=GETDATE() -- Sample date
SELECT DAY(DATEADD(mm,1, DATEADD(dd, - Day(@sample),@sample)))

Unknown said...
This comment has been removed by the author.
Anonymous said...

ya good

Unknown said...

The query is working absolutely fine.
Thanks to the guy who has shared this information.

Rahul said...

Good 1 thanks

Anonymous said...


Unknown said...

we can use this also.

declare @i int,@d1 datetime,@d2 datetime
select @i=day('2/12/2013')-1

select @d1= dateadd(dd,-@i,'2/12/2013')
select @d2=dateadd(mm,1,@d1)
select datediff(dd,@d1,@d2)

Anonymous said...

declare @Date datetime,@days INT
SELECT @Date=GETDATE(),@days =(day(@date)-1)

sanju said...

Very Nice Code. Its really save much time to do logic in c#

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.