Thursday 21 May 2009

Get number of days in a month using SQL

Here is a sample to get the number of days in a month, for a given month and a year

declare @zMonth as varchar(2), @zYear as varchar(4)
set @zMonth = '02'
set @zYear = '2009'

select datediff(day,cast(('01-' + @zMonth + '-' + @zYear) as datetime),dateadd(day,-1,dateadd(month,1,cast(('01-' + @zMonth + '-' + @zYear) as datetime)))) + 1


**Please note: The date format of the machine that is running SQL should be 'dd/MM/yyyy'. Or please change the order in query prior using it. Or use the following Code. (Thanks to Rohan for pointing that out)
  
declare @zMonth as varchar(3), @zYear as varchar(4)
set @zMonth = 'Feb'
set @zYear = '2009'

select datediff(day,cast(('01-' + @zMonth + '-' + @zYear) as datetime),dateadd(day,-1,dateadd(month,1,cast(('01-' + @zMonth + '-' + @zYear) as datetime)))) + 1

2 comments:

  1. You just made something that I thought was so difficult be, truly, so easy! Thanks for the post!

    ReplyDelete
    Replies
    1. There's even better workaround in SQL 2012.

      SELECT DATEPART(DAY,EOMONTH(GETDATE()))

      Just replace the 'GETDATE()' with the 1st of the required month.

      Delete