sql code to get week number for FiscalYear

Question

Need sql code to get week number for FiscalYear – Date table ranges from 1975 to 2024

Already have weekno (WeekOfYear) for CalendarYear -week count start from 1st of April and end on 31st march -Week start from Sunday to Saturday -On Fiscalyear end, week also ended

eg. 1st apr 2016 -Week 1 2nd apr 2016 - week 1 3rd apr 2016 to 9th apr 2016 -week 2 10th apr 2016 to 16th apr 2016 -week 3 ... and so on

I'd highly recommend creating a date table that will hold this information. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/ — Rich Benner, Nov 14 '16 at 16:44
Possible duplicate of [How to get week number of the month from the date in sql server 2008](http://stackoverflow.com/questions/13116222/how-to-get-week-number-of-the-month-from-the-date-in-sql-server-2008) — Neo, Nov 14 '16 at 16:47
@ mister - it isn't a duplicate of week number because that other question does not seem to mention the fiscal year, and the example given nowhere near fits in with the examples her - fiscal week 48 is not in November — Cato, Nov 14 '16 at 16:58

Mohamad TAGHLOBI · Answer 1 · 2016-11-15T09:53:56.653

Writing a query to do this, seems to me very complicated. I advise you to do it either by using a table (Choice1) or by creating an EXCEL file (Choice2).

Choice1: Create a WeekCalendarForFiscalYear table with 4 fields [DateOfToday, weekNumber, FiscalYear].

In DateOfToday, you put the date of the day.
In WeekNumber, you put the number of the fiscal week.
In FiscalYear, you are in the fiscal year (2016, 2017, 2018, etc.).

You have to think about indexing these fields.

/!\ : You can also create your table like this: WeekCalendarForFiscalYear with 4 fields [WeekNumber, FiscalYear, StartDate, EndDate]. StartDate would correspond to the beginning of the fiscal week and EndDate at the end of the fiscal week.

Choix2: You create an EXCEL file in which you enter the data either as in Choix1, or as it suits you.

The file must be physically on the server of the database.
You access the file via OPENROWSET. You will find on the web a wide range of sites that will help you to use OPENROWSET well.

Hope this can help.

score 0 · Answer 2 · answered Nov 14 '16 at 17:25

assuming DATEFIRST is 7, I got this function - actually you can default datefirst = 7 for the lifetime of your query only - this seems to work according to by 1980 calendar

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION FiscalYearMonth
(
    @DAT datetime

)
RETURNS int
AS
BEGIN

    DECLARE @TAXY as int;
    DECLARE @R as int;

    SET @TAXY = YEAR(dateadd(month,-3,@dat));
    DECLARE @TDAT as  varchar(8) 

    SET @TDAT = CAST(@TAXY AS varchar(4)) + '0401';

    SELECT @R = datediff(day,  DATEADD(day , (8 - DATEPART(DW,@TDAT)) % 7,@TDAT), dateadd(day,14,@DAT)) / 7;

    RETURN @R;

END
GO

e.g.

 SET datefirst   7;
select dbo.FiscalYearMonth('19800413')

sql code to get week number for FiscalYear

2 Answers2