mardi 30 juin 2015

How to get month number in sql by passing the week number

This is my function as below

CREATE FUNCTION  getmonth
(        

 @startdate  datetime,@Week int
)        
RETURNS  int        
AS        
BEGIN                      
 declare @year int,@Month int;              
 declare @sdate  datetime=@startdate;       
 set @year= year(CAST(@startdate as date));  
 set @sdate = DATEADD(wk, DATEDIFF(wk, 6,  '1/1/' + CAST( @year as varchar(4)) ) + (@Week-1) ,1);           
  SET @Month = month( @sdate);              
 return @month        

END        

I Just want this function to return the month number when we pass a week number of the year and week start date of year.

Week should be start on Sunday and ends at Saturday.

Example

For Year 2015,Week start date is 12/28/2014.When we pass week number it should return the month number of year 2015.

Aucun commentaire:

Enregistrer un commentaire