Post reply

Function to return the fiscal month from a date. Fiscal year begins in October

  • July 26, 2013 at 10:59 am

    #275724

    I need a function that would return the fiscal month number from a date. Our fiscal year starts in October.

    Something like...

    declare @Date datetime

    if month(@Date)>9 then month(@Date) -9 else month(@Date) + 3

  • July 26, 2013 at 11:06 am

    #1636250

    One solution is to create a Calendar table.

    You can have as many date related fields as needed.

    The structure might be something like this:

    Date CalendarMonth CalendarYear FiscalMonth FiscalYear FiscalPeriod

    You could have a function return the required values, or just by a join.

    Hope this helps.

    Bill

  • July 26, 2013 at 11:15 am

    #1636251

    I wanted it to be more generic and not be tied to a table.

  • July 26, 2013 at 11:25 am

    #1636256

    NineIron (7/26/2013)

    I need a function that would return the fiscal month number from a date. Our fiscal year starts in October.

    Something like...

    declare @Date datetime

    if month(@Date)>9 then month(@Date) -9 else month(@Date) + 3

    What about using the dateadd function in combination with month? Something along the lines of:

    SELECT MONTH(DATEADD(M, 3, @Date))


    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • July 26, 2013 at 11:38 am

    #1636261

    Then October, November and December would be 13, 14 and 15.

  • July 26, 2013 at 11:41 am

    #1636263

    NineIron (7/26/2013)

    Then October, November and December would be 13, 14 and 15.

    No, the dateadd function takes care of that. October wraps around to January of the next year, then the month function returns 1.


    The opinions expressed herein are strictly personal and do not necessarily reflect the views or policies of my employer.

  • July 26, 2013 at 11:42 am

    #1636264

    No, it won't. Unless they add 3 new months to the calendar.:-D

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • July 26, 2013 at 11:44 am

    #1636266

    Wonderful. Thanx.

Viewing 8 posts - 1 through 7 (of 7 total)

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