Week number starting November

Transact-SQL
1

Hei, I'm trying to fill a column with the "week number" based on another column that has the date as MM/dd/yyyy. My problem is that "week 1" should start from the 1st of November. i know how to do it if the fiscal year starts from 1st of Januar using datepart(week, column) but i cant figure it out for the start date in november.
i would appreciate your help. thanks!

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
3

I'm not sure what day of the week you consider "start of week" or the details of how you want to number weeks, but you should be able to tweak this to get exactly the results you need:

SELECT mmddyyyy_column,
    DATEDIFF(DAY, nov_01, CAST(dates.mmddyyyy_column AS date)) / 7 AS week#
FROM (
    VALUES('12/13/2016'),('01/22/2017'),('05/23/2017'),('10/31/2017')
    ) AS dates(mmddyyyy_column)
CROSS APPLY (
    SELECT CAST(CAST(YEAR(mmddyyyy_column) + CASE WHEN MONTH(mmddyyyy_column) < 11 THEN -1 ELSE 0 END AS char(4)) + '1101' AS date) AS nov_01
) AS ca1
4

thaink you, you were very helpful :slight_smile: