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)Output:
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))))
28 Days
0 comments:
Post a Comment