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

Question

Post reply

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

NineIron

SSChampion

Points: 12536
More actions
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

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

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

Bill-89778 Ten Centuries Points: 1201 More actions · Answer 1

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

NineIron SSChampion Points: 12536 More actions · Answer 2

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

LightVader Hall of Fame Points: 3810 More actions · Answer 3

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.

NineIron SSChampion Points: 12536 More actions · Answer 4

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

LightVader Hall of Fame Points: 3810 More actions · Answer 5

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

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

NineIron SSChampion Points: 12536 More actions · Answer 7

NineIron

SSChampion

Points: 12536

July 26, 2013 at 11:44 am

#1636266

Wonderful. Thanx.