Friday, August 3, 2012

Fiscal Year Calculation in SQL Server

Fiscal Year Calculation in SQL Server

DECLARE @SuppliedDate DATETIME,
@StartMonth INT,
@SessYr NVARCHAR(7)
Set @SuppliedDate='2012-01-01'
Set @StartMonth=10

 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

OR

DECLARE @BusinessDate datetime
Set  @BusinessDate='2012-10-01'

SELECT MONTH(@BusinessDate)as BMonth,YEAR(@BusinessDate) as BYear,FiscalYear = CASE
WHEN MONTH(@BusinessDate) < 10 THEN YEAR(@BusinessDate)-1
ELSE YEAR(@BusinessDate) END
                   


No comments:

Post a Comment