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:

[code language=”sq”]
SELECT DATEPART( wk, GetDate())
[/code]

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)

[code language=”sql”]

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
[/code]

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.

[code language=”sql”]
Select FiscalWeek(’07’,’01/01/2014′)
[/code]

Returns 27

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!