FISCAL_WEEK from 1 Jan till First Friday

  • 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.

    118381-image

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

    Thanks,

    Mahesh

  • This is actually pretty easy to do based on what you put there.  Step 1, set Saturday as your start date by running:

    SET DATEFIRST 6

    Next, you are going to be looking at the DATEPART function, so something like this:

    SELECT DATEPART(WEEK,Fiscal_Date)

    Those 2 commands should generate your FISCAL_WEEK column for you!

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply