Fiscal Year Functions for SQL Server

These aren't particularly sophisticated, but we had a sudden need to do some reporting based on a vendor's fiscal year, and this should get the job done. Use freely (at your own risk).

CREATE FUNCTION dbo.first_day_of_fiscal_year(@date datetime, @fiscal_year_start_month int, @fiscal_year_start_day int)
RETURNS date
AS
BEGIN
    DECLARE @fy_start datetime = CAST(DATETIMEFROMPARTS(YEAR(@date), @fiscal_year_start_month, @fiscal_year_start_day, 0, 0, 0, 0) AS date)
    IF @fy_start > @date
        SET @fy_start = DATEADD(year, -1, @fy_start)
    RETURN @fy_start
END

GO

CREATE FUNCTION dbo.last_day_of_fiscal_year(@date datetime, @fiscal_year_start_month int, @fiscal_year_start_day int)
RETURNS date
AS
BEGIN
    RETURN DATEADD(dy, -1, DATEADD(year, 1, dbo.first_day_of_fiscal_year(@date, @fiscal_year_start_month, @fiscal_year_start_day)))
END

Categories

Tags

Other Things