/****** Object: UserDefinedFunction [dbo].[fnGLGetFiscYear_Toy] Script Date: 07/18/2012 12:47:45 ******/ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGLGetFiscYear_Toy]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) DROP FUNCTION [dbo].[fnGLGetFiscYear_Toy] GO /****** Object: UserDefinedFunction [dbo].[fnGLGetFiscYear_Toy] Script Date: 07/18/2012 12:47:45 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[fnGLGetFiscYear_Toy] ( @CompanyID VARCHAR(3), @TranDate DATETIME ) RETURNS VARCHAR(5) AS BEGIN DECLARE @FiscYear VARCHAR(9) SELECT @FiscYear = FiscYear FROM tglFiscalPeriod WHERE CompanyID = @CompanyID AND @TranDate >= StartDate AND CONVERT(DATETIME,(CONVERT(VARCHAR(10), @TranDate, 101))) <= EndDate RETURN @FiscYear END GO GRANT Execute ON [dbo].[fnGLGetFiscYear_Toy] TO ApplicationDBRole GO
I had to do some summarized reporting by Fiscal Year. Thought this might help others.