Get current and previous Fiscal year start date and end date in SQL Server

  • Article

Recently, a user requested to develop a report. Report was the production of current and previous year.

Requirement was

  • User will input any random date.
  • Based on that date the report should display the production of current fiscal year as well as previous fiscal year.

Task was little bit difficult because you have to get current as well previous fiscal year dates from the given input date. To accomplish that task a SQL function was created.

This might helpful to others.

Comments and corrections are welcome.

/*
Date:        29-10-2015
Purpose:    Function to get first and last date current and previous fiscal year
*/
Create FUNCTION dbo.getFiscalDt (@Date date)
RETURNs @FiscalDates table
        (
            FiscalDate date
        )    
AS        
BEGIN         
        
            DECLARE @CurrYear_StartDT DATE
            DECLARE @CurrYear_EndDT DATE
            DECLARE @Prevyear_StartDt DATE
            DECLARE @Prevyear_EndDt DATE

            /*---Current Year--*/
            SET @CurrYear_StartDT=    (SELECT CAST(CONVERT (varchar(4),YEAR(@Date)-1) + '-' + '04' + '-' + '01' AS DATE))
            SET @CurrYear_EndDT    =    (SELECT CAST (CONVERT(varchar(4),YEAR(@Date)) + '-' + '03' + '-' + '31' AS DATE))
            /*---Previous Year--*/
            SET @Prevyear_StartDt=     (SELECT CAST(CONVERT (varchar(4),YEAR(@Date)-2) + '-' + '04' + '-' + '01' AS DATE))
            SET @Prevyear_EndDt=    (SELECT CAST (CONVERT(varchar(4),YEAR(@Date)-1) + '-' + '03' + '-' + '31' AS DATE))
            /**/
            INSERT INTO @FiscalDates (FiscalDate) VALUES (@CurrYear_StartDT),(@CurrYear_EndDT),(@Prevyear_StartDt),(@Prevyear_EndDt)
            RETURN            
END

/*Call Function Here
WITH GetFiscalDate
AS
(
    SELECT p.FiscalDate,p.ID FROM (SELECT RANK () OVER (ORDER BY FiscalDate asc)ID,FiscalDate  FROM  dbo.getFiscalDt('2010-11-29'))p
)
SELECT FiscalDate FROM GetFiscalDate WHERE ID=3
/* End*