Financial Month

,

Use this function wherever you want to have a index based on the financial Monther Order rather then Calander Month Order

 

This function will return the respective Financial Month Code, when the Calander Month Code is given as input for this function

 

e.g. For April, The Calander Month Code is '04' where as the Financial Month Code is '01' as all financial years started with April.

Similarly for January the Calander Month Code is '01' whereas the Financial Year Code is '10'

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Fn_FinMonth]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[Fn_FinMonth]
go
Create Function dbo.Fn_FinMonth(@MonthCode varchar(2))  
Returns Varchar(2)  
As 
/*------------------------------------------------------------------------------------------------------------------------------------------
** SQLVersion : SQL 2000
** Function	: Fn_FinMonth
** Author 	: Kartik M
** DateTime	: 29 January 2011 22:30
** Version	: 1.2
** Purpose	: To get the financial month from the respective calander month
** ToCheck	:
** Changes	: KM - 06 Feburary 2011 - Change the return type form Numeric to Varchar and add Leading Zeros to avoid problem while sorting
------------------------------------------------------------------------------------------------------------------------------------------*/ 
/*
-- Start of Debugging Stuff
	Declare @MonthCode varchar(2)
	Set @MonthCode = '02' -- Pass the Month Number as Input
-- End of Debugging Stuff
*/
Begin  
	Declare @FinMonthC Varchar(2)  
	Declare @IMonthCode int  
  
	Set @IMonthCode = cast(@MonthCode as int)  
	if @ImonthCode between 4 and 12  
		Begin  
			Set @FinMonthC = @IMonthCode-3
			Set @FinMonthC = Replicate('0', 2-len(@FinMonthC))+@FinMonthC
		End  
		
	Else if @ImonthCode between 1 and 3  
		Begin  
		  	Set @FinMonthC = @IMonthCode+9
			Set @FinMonthC = Replicate('0', 2-len(@FinMonthC))+@FinMonthC
		End  
   
Return @FinMonthC
End  
--Select dbo.Fn_FinMonth ('05')  
go

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)