Making Financial Year Function In SQL Server

Hi,

Many times we required to add financial year with our invoice or po number to make it suitable for client as like PO/2011-12/04/001  here 2011-12 is current financial year. For this purpose we should make a function for getting current financial year, which can be used in insert update or select statement.

Here is a function which returns financial year as varchar for the given datetime or we can use getdate() function for getting current date financial year.


CREATE FUNCTION [dbo].[FinencialYear]
(@Date DATETIME)
RETURNS VARCHAR(7) AS
BEGIN
DECLARE @RetVal varchar(7)
Declare @Month int
Declare @Year int
Select @Month=Month(@Date)
Select @Year=Year(@Date)
-- Assuming that the financial year is starting from april
if @Month>3
 Begin
Set @RetVal=cast(@Year As varchar(10))+'-'+cast(right('0' + rtrim(@Year+1),2) as varchar(2))
End
Else
Begin
Set @RetVal=cast(@Year-1 as varchar(10))+'-'+cast(right('0' + rtrim(@Year),2) as varchar(2));
End
Return @RetVal
End

We can use this function as ordinary function as Select dbo.FinencialYear(getdate()) or replace getdate() with any date for which you want to get financial year.

Thanks..