SQL Server: Function to get fiscal year or, session year

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

2 thoughts on “SQL Server: Function to get fiscal year or, session year

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s