Week number starting November

Transact-SQL
2

This might get you started:

declare @fiscal_monthday smallint=0903;

select yourdatefield
      ,datediff(day
               ,dateadd(year
                       ,case
                           when datepart(month,yourdatefield)*100+datepart(day,yourdatefield)
                               <@fiscal_monthday
                           then -1
                           else 0
                        end
                       ,dateadd(day
                               ,@fiscal_monthday%100-1
                               ,dateadd(month
                                       ,floor(@fiscal_monthday/100)-1
                                       ,dateadd(year,datediff(year,0,yourdatefield),0)
                                       )
                               )
                       )
               ,cast(yourdatefield as date)
               )
      /7+1 as fiscal_week
  from yourtable
;

This is another way:

declare @fiscal_month char(2)='09'
declare @fiscal_day char(2)='03';

select yourdatefield
      ,datediff(day
               ,dateadd(year
                       ,case
                           when datepart(month,yourdatefield)*100+datepart(day,yourdatefield)
                               <cast(@fiscal_month+@fiscal_day as int)
                           then -1
                           else 0
                        end
                       ,cast(datename(year,yourdatefield)+'-'+@fiscal_month+'-'+@fiscal_day as date)
                       )
               ,cast(yourdatefield as date)
              )
      /7+1 as fiscal_week
  from yourtable
;

And yet another way:

declare @fiscal_month char(2)='09'
declare @fiscal_day char(2)='03';

select yourdatefield
      ,datediff(day
               ,cast(str(datepart(year,yourdatefield)
                        +case
                            when datepart(month,yourdatefield)*100+datepart(day,yourdatefield)
                                <cast(@fiscal_month+@fiscal_day as smallint)
                            then -1
                            else 0
                         end
                        )
                    +'-'+@fiscal_month+'-'+@fiscal_day
                     as date
                    )
               ,cast(yourdatefield as date)
               )
      /7+1 as fiscal_week
  from yourtable
;
1 Like