If your fiscal year matches the calendar year then SQL has an easy process of find the week number. You simply use something like this:
SELECT DATEPART( wk, GetDate())
but what if your fiscal year is other than the calendar year. (Which is the case where I work). You can use something like this solution. I would create a function so that you can use it in stored procedures and regular t-sql statements.
I have to give credit to Paul Alcon. After wading through his article I have made some comments and presented it here. (See comments inside function)
Create function FiscalWeek (@startMonth varchar(2), @DateToFind datetime) returns int as Begin --@StartMonth = > The month number of the start start of fiscal period --@DateToFind = > The date that you want to find the fiscal week number declare @firstWeek datetime declare @weekNum int declare @year int --Step 1 set @year = datepart(year, @DateToFind)+1 --Step 2: you are taking the 4th day of month of next year, this will always be in week 1 set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102) --Step 3: Retreat to beginning of the week for the date set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek) --Step 4: --This allows you to pass in any date with year to find the fiscal week number while @DateToFind < @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 = ((datediff(day, @firstweek, @DateToFind)/7)+1) return @weekNum end
Let’s take a specific example.
Assume that your period starts the first week in July. Then you pass in 07 as your @StartMonth and you want to find which week number 01/01/2014 resides in. Your script would look like this.
Step 1: the script takes the current year of 2014 and adds 1 year making @year=2015
Step 2: The script first takes 07 and adds 4 days and adds the year from Step 1 (making @firstWeek = 07/04/2015)
Step 3: goes back and gets the first day of the week therefore it would be 06/28/2015 (Sunday)
Step 4: This goes back to find the year which your date is being calculated for and then takes the first day of that year and gets the number of days between that year and the fiscal end date divides by 7 days and rounds up.
Note: I have yet to find a problem with the logic using this sql function.
Looking for quality web hosting? Look no further than Arvixe Web Hosting!