Getting Fiscal Week


CREATE function [dbo].[FiscalWeek]  (@startMonth varchar(2), @myDate datetime) 
returns int 
as 
begin 
declare @firstWeek datetime 
declare @weekNum int 
declare @year int 
set @year = datepart(year, @myDate)+1 
--Get 4th day of month of next year, this will always be in week 1 
set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102) 
--Retreat to beginning of week 
set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek) 
while @myDate < @firstWeek --Repeat the above steps but for previous year 
 begin 
  set @year = @year - 1 
  set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102) 
  set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek) 
 end 
set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1) 
return @weekNum 
end  

Comments

Popular posts from this blog

Who Deleted and Drop the record in table

Generate Password

Key Board Shortcuts