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