I have written a simpler date table function without the Financial Week and Year: Simpler Date Table Function
Please note that the first Financial Week of a year in the below script ends on the 1st Friday of April. The second week ends in the next Friday and so on.
/* Example: select * from [dbo].[udf_DailyDateTable]('5 dec 2018', '23 feb 2019') */ create function [dbo].[udf_DailyDateTable] (@StartDate datetime, @EndDate datetime) returns @DailyDatesWithFin table ( DailyDatesID int identity, DailyDate date, DayNumber int, DayName varchar(50), DayOfTheWeek int, WeekNumber int, QuaterNumber int, MonthNumber int, MonthCalled varchar(50), YearNumber int, FinWeek int, FinYear int, FinWeekStart date, FinWeekEnd date ) AS BEGIN ---- ------------ Test ------------- ------ -- select * from [dbo].[udf_DailyDateTable] ('1 jan 2019', '5 jan 2019') -- declare @DailyDatesWithFin table -- ( -- DailyDatesID int identity, -- DailyDate date, -- DayNumber int, -- DayName varchar(50), -- DayOfTheWeek int, -- WeekNumber int, -- QuaterNumber int, -- MonthNumber int, -- MonthCalled varchar(50), -- YearNumber int, -- FinWeek int, -- FinYear int, -- FinWeekStart date, -- FinWeekEnd date -- ) -- declare @StartDate date, @EndDate date -- set @StartDate = '1 jan 2016' -- set @EndDate = '3 feb 2017' ------------------------------ if @StartDate > @EndDate return -------- Keep the Dates passed reserved to calculate Financial Week and Year ---- declare @dateFrom date = @StartDate, @dateTo date = @EndDate ------------------------------------------------------------------------ declare @DailyDates table ( DailyDate date, DayNumber int, DayName varchar(50), DayOfTheWeek int, WeekNumber int, QuaterNumber int, MonthNumber int, MonthCalled varchar(50), YearNumber int ) while (@StartDate <= @EndDate) begin insert into @DailyDates select @StartDate DailyDate , datename(day, @StartDate) DayNumber , datename(dw, @StartDate) DayName , DATEPART(dw, @StartDate) DayOfTheWeek -- -- Default 1 = Monday ("SET DATEFIRST 1") , datepart(week, @StartDate) WeekNumber , DATEPART(qq , @StartDate) as QuarterValue , datepart(month,@StartDate) MonthNumber , datename(month,@StartDate) MonthCalled , YEAR(@StartDate) YearNumber set @StartDate = dateadd(day, 1 ,@StartDate) --print @StartDate -- test ` end --select * from @DailyDates -- test --================================================== ------ Get Financial Week And Year ---------------- --================================================== -------------------- Test ------------- --declare @dateFrom date = '1 jan 2019' --, @dateTo date = getdate() ----------------------------------------- --------- Find the April and March dates required to find the financial dates ------------ declare @yearFrom int if month(@dateFrom) 3 -- end next year if @dateTo is after March set @yearTo = year(@dateTo) + 1 else set @yearTo = year(@dateTo) declare @FinEndDate date = datefromparts(@yearTo, 3, 31) -- Ends at the end of next year March --select @FinStartDate as StartDate , @FinEndDate as EndDate , @dateFrom DateFrom, @dateTo DateTo -- test ------------------------------------------------------------------------------------ declare @FinCal table ( FinWeek int , FinYear int , FinWeekStart date , FinWeekEnd date ) declare @FinWeekCounter int = 1 Declare @FridayDateCheck date Declare @FinWeekStart date = @FinStartDate -- find the frist Friday of April which markds the end of week 1 while @FinStartDate = 50 if month(@FinStartDate) = 4 set @FinWeekCounter = 1 end --- test --select * from @FinCal --where FinWeekStart between @dateFrom and @dateTo --or @dateFrom between FinWeekStart and FinWeekEnd -- this gets any week where @dateFrom begins in the previous week --order by FinWeekStart, FinWeek insert into @DailyDatesWithFin select * from @DailyDates dd join @FinCal fc on dd.DailyDate between FinWeekStart and FinWeekEnd where FinWeekStart between @dateFrom and @dateTo or @dateFrom between FinWeekStart and FinWeekEnd -- this gets any week where @dateFrom begins in the previous week order by FinWeekStart, FinWeek --select * from @DailyDatesWithFin return END
[…] There is an extended version of this table with financial week and year here. […]