You can use this function to get the fiscal/financial year from a supplied date value. This may be useful when you have a datetime column in a table but you want to show it’s fiscal year. Another case when you may need this function is you have a datetime column in your table but you have to show the transaction made, specified fiscal year/session year wise or when you are preparing any statistical report.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Suvendu> -- Create date: <10 mar 2012> -- Description: <returns the fiscal year of the supplied date> -- ============================================= CREATE FUNCTION [dbo].[fuGetFiscalYear] ( @SuppliedDate DATETIME ) RETURNS NVARCHAR(7) AS BEGIN DECLARE @SessYr NVARCHAR(7) SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>3 THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END RETURN @SessYr END GO
[Note: I have assumed the fiscal year as Indian fiscal year or, financial year. You can change it accordingly or, can use the below function]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Suvendu> -- Create date: <10 mar 2012> -- Description: <returns the fiscal year of the supplied date> -- ============================================= CREATE FUNCTION [dbo].[fuGetSessionYear] ( @SuppliedDate DATETIME, @StartMonth INT ) RETURNS NVARCHAR(7) AS BEGIN DECLARE @SessYr NVARCHAR(7) SELECT @SessYr= CASE WHEN DATEPART(MONTH,@SuppliedDate)>@StartMonth THEN CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate)+1 AS VARCHAR(4)),2) ELSE CAST(DATEPART(YEAR,@SuppliedDate)-1 AS VARCHAR(4))+'-'+RIGHT(CAST(DATEPART(YEAR,@SuppliedDate) AS VARCHAR(4)),2) END RETURN @SessYr END GO
Thanks for that awesome posting. It saved MUCH time 🙂
You have provided an awesome resource.