How would you calculate the fiscal year from a date field in a view in SQL Server?
+1
A:
I don't think you can, because there is no universal fiscal calendar. Fiscal years vary between businesses and countries.
ADDENDUM: What you would need to do is have a separate DB table consisting of a fiscal start date, and a fiscal end date for each applicable year. Use the data in that table to calculate the fiscal year given a particular date.
alex
2009-11-20 17:22:16
I would **NOT** use a table as Fiscal year isn't something that should be that dynamic.
Brett Veenstra
2009-11-20 17:26:19
Accounting is standard between countries, and the common convention is that the fiscal year starts on April 1st, ending on March 31st. A company's fiscal year is based on their incorporation date.
OMG Ponies
2009-11-20 17:30:26
@Brett - well fine, if you're really worried about someone editing the data in the fiscal date table...
alex
2009-11-20 17:31:42
I would downvote Ponies' comment if I could. Fiscal years can be anything you want and are far from standard anywhere.
No Refunds No Returns
2009-11-20 17:32:52
Fiscal years are what you file with your taxing authority. It's only your accountants sanity that drives this.
Brett Veenstra
2009-11-20 17:37:14
@Don: I work for a Canadian accountant
OMG Ponies
2009-11-20 18:08:37
+1
A:
You would need more than a single field to do this...
You should check your definition of fiscal year as it varies from company to company
Andrew G. Johnson
2009-11-20 17:23:52
OMG Ponies
2009-11-20 17:31:57
That might be the case in the US - but the world doesn't end at the US border......
marc_s
2009-11-20 17:35:03
Company's fiscal year *can be changed* in coordination with your government. There are limitations of course, but it can be done (in the US as well).
Brett Veenstra
2009-11-20 17:58:48
+5
A:
I suggest you use a User-Defined Function based on the Fiscal year of your application.
CREATE FUNCTION dbo.fnc_FiscalYear(
@AsOf DATETIME
)
RETURNS INT
AS
BEGIN
DECLARE @Answer INT
-- You define what you want here (September being your changeover month)
IF ( MONTH(@AsOf) < 9 )
SET @Answer = YEAR(@AsOf) - 1
ELSE
SET @Answer = YEAR(@AsOf)
RETURN @Answer
END
GO
Use it like this:
SELECT dbo.fnc_FiscalYear('9/1/2009')
SELECT dbo.fnc_FiscalYear('8/31/2009')
Brett Veenstra
2009-11-20 17:24:54
Just so the question asker knows, he'll probably have to add more logic here, because fiscal start and end dates often change from year to year. They do where I work.
alex
2009-11-20 17:38:33
True, using a UDF consolidates your logic in a more protected way than table based approaches do.
Brett Veenstra
2009-11-20 18:34:00
A:
I totally agree with alex, I've been involved in development and implementation of various ERPs, local (ME) and global. having a Fiscal year table and Periods as well is the way to go.
Laplace
2010-07-20 12:14:24