August 23, 2012

Get number of days in a month by passing date in sql function


Get number of days in a month by passing date in sql function

 CREATE FUNCTION [dbo].[GetNoOfDaysInMonthfromdate] ( @DateParm  DATETIME )  
 RETURNS INT  
 AS  
 BEGIN  
   RETURN CASE WHEN MONTH(@DateParm) IN (1, 3, 5, 7, 8, 10, 12) THEN 31  
         WHEN MONTH(@upDate) IN (4, 6, 9, 11) THEN 30  
         ELSE CASE WHEN (YEAR(@DateParm) % 4  = 0 AND  
                 YEAR(@DateParm) % 100 != 0) OR  
                 (YEAR(@DateParm) % 400 = 0)  
              THEN 29  
              ELSE 28  
            END  
       END  
 END  
 GO