FISCAL_WEEK from 1 Jan till First Friday

Question

Hi,

We have one requirement. Wee need to create a fiscal_week column in SQL_server table.
Table will have data as Normal_date column as fiscal_date and fiscal_year colum which will have year part of the date.

Logic for FISCAL_WEEK is as below.

• FIRST FISCAL WEEK WILL START FROM 1 JAN OF EVERY YEAR AND IT WILL BE TILL FIRST FRIDAY.
• SECOND WEEK STARTS FROM SATURDAY AND IT WILL RUN TILL NEXT FRIDAY.
• THIS WILL GO ON TILL THE END OF YEAR (31 JAN)

We will Have data something as below table.

How would I create query for this ?We will have data from 2010 till 2035 years in the table.

Thanks,
Mahesh

Answer 1

Hi,

Thank you for reply.

I got answer as below.

SET DATEFIRST 6
GO

SELECT FISCAL_DATE, DATEPART(WEEK,Fiscal_Date) FISCAL_WEEK FROM ERP.CUSTOM_FISCAL_CALENDER

Thanks,
Mahesh

Answer 2

Since the function DATENAME(week, FISCAL_DATE)) returns the number of the week starting on Sunday and ending on Saturday, you can use a CASE statement to check if DATENAME(weekday, FISCAL_DATE) is Saturday. If yes, FISCAL_WEEK = CONVERT(tinyint, DATEPART(week, FISCAL_DATE)) + 1. Otherwise FISCAL_WEEK = CONVERT(tinyint, DATEPART(week, FISCAL_DATE)).