Calculate fiscal year in SQL Server

sql-server accounting
R0b0tn1k picture R0b0tn1k · Nov 20, 2009 · Viewed 78.2k times · Source

How would you calculate the fiscal year from a date field in a view in SQL Server?

Answer

Brett Veenstra picture Brett Veenstra · Nov 20, 2009

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')

Related questions

Accounting System for Winforms / SQL Server applications

If you were going to write a vertical market C# / WinForms / SQL Server application and needed an accounting "engine" for it, what software package would you chose ? By vertical market, I mean the application is intended to solve a particular …

Read More
Accounting and Database design, storing debit and credit amount

QUESTION: In the case below should I have stored all my amount as positives decimal amounts then flag the amount as either being a "Debit" or "Credit" rather than storing debits as negative amount and credits as positive amount? In …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More