Finding Week Number in SQL Based on Fiscal Period

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.

Select FiscalWeek('07','01/01/2014')

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!

Tags: , , , , , , , , , | Posted under Programming/Coding | RSS 2.0

Author Spotlight

David Bauernschmidt

David Bauernschmidt

I live in the historical triangle of Virginia where I am married with two daughters. I have spent over 13 years working for a Fortune 500 company in the computer area. I started in VB 6.0 and by the time I ended my employment I was supervising a development team where we built many web applications. When my first daughter was born I wanted to spend more time with her so I left and became a programmer analyst for local government as well as launch my own company. Since then I have grown James River Webs into a profitable web design and application company helping small businesses create a big presence on the internet. As an employee I have created web application used by citizens and other companies. I enjoy fly fishing, and spending time with my family. I also enjoy learning new approaches and development tools when it comes to developing applications.

2 Comments on Finding Week Number in SQL Based on Fiscal Period

  1. Mike says:

    12/29/2014 yields week 53 when it is actually week 1 when using the “first four days” method of getting weeks. as an example, in MS Outlook, in the calendar, 12/29/14 is week 1 and there is no week 53.
    Do you have any ideas on how to rectify this?

    Thanks,
    Mike

Leave a Reply

Your email address will not be published. Required fields are marked *


5 − = 4

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>