Get the Number of Days in a Month for Given Month and Year

With this sample code user can get the number of days in the Given Month and Year.

User's Input Data: '02-2009' or '02/2009'

There are two method to do this:

First Method:

DECLARE @Date varchar(20),@FirstDay varchar(2), @Seperator char(1)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Date=SUBSTRING(@Date,1,3) + @FirstDay + @Seperator + SUBSTRING(@Date,4,7)
SELECT DATEDIFF(DAY,Convert(Datetime,@Date), DATEADD(MONTH,1,Convert(Datetime,@Date))) 'Days'


Second Method:

DECLARE @Date varchar(20),@Month int, @NextMonth varchar(20), @Year varchar(5),@Seperator char(1)
DECLARE @FirstDay varchar(2)
SET @Date='02-2009'
SET @FirstDay='01'
SET @Year=SUBSTRING(@Date,4,7)
SET @Seperator=SUBSTRING(@Date,3,1)
SET @Month =Convert(int,(SUBSTRING(@Date,1,2)))+1
SET @Date=SUBSTRING(@Date,1,3)+@FirstDay+@Seperator+@Year
IF(@Month>12)
BEGIN
SET @Month=1
SET @Year=CONVERT(varchar(5), (CONVERT(int, @Year)+1))
END

SET @NextMonth=convert(varchar(2), @Month) + @Seperator + @FirstDay+@Seperator+@Year
SELECT Convert(int,((Convert(Datetime,@NextMonth)) - (Convert(Datetime,@Date))))


Output:
28 Days

0 comments: