Post reply

FISCAL_WEEK from 1 Jan till First Friday

  • July 27, 2021 at 2:31 pm

    #3914323

    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

  • July 27, 2021 at 3:27 pm

    #3914337

    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!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

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

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