Get Financial Year from Current Date in SQL Server

by

In this comprehensive article, I’ll walk you through different methods to get the financial year from the current date in SQL Server, explain the logic behind them, and provide practical examples.

Get Financial Year from Current Date in SQL Server

Calculating the financial year dynamically in SQL Server is essential for:

  • Financial reporting: Grouping transactions by fiscal year.
  • Budgeting and forecasting: Aligning data with budgetary periods.
  • Compliance: Meeting regulatory reporting requirements.
  • Data analysis: Comparing performance across fiscal years.

Let us discuss all the possible methods to get the financial year from date in SQL Server.

Method 1: Using a CASE Statement to Get the Financial Year Based on the Current Date

The simplest way to calculate the financial year in SQL Server is by using a CASE statement that compares the current month and adjusts the year accordingly.

Example: Financial Year Starting July 1

DECLARE @CurrentDate DATE = GETDATE();

SELECT 
    CASE 
        WHEN MONTH(@CurrentDate) >= 7 THEN CAST(YEAR(@CurrentDate) AS VARCHAR(4)) + '-' + CAST(YEAR(@CurrentDate) + 1 AS VARCHAR(4))
        ELSE CAST(YEAR(@CurrentDate) - 1 AS VARCHAR(4)) + '-' + CAST(YEAR(@CurrentDate) AS VARCHAR(4))
    END AS FinancialYear;

Explanation:

  • If the current month is July (7) or later, the financial year spans the current year and the next year.
  • Otherwise, it spans the previous year and the current year.

After executing the above query, I got the expected output as shown in the screenshot below.

Get Financial Year from Current Date in SQL Server

Method 2: Creating a User-Defined Function (UDF) for Financial Year

To reuse this logic across your database, I recommend creating a scalar function.

Step 1: Create the Function

CREATE FUNCTION dbo.GetFinancialYear(@InputDate DATE)
RETURNS VARCHAR(9)
AS
BEGIN
    DECLARE @FY VARCHAR(9);

    SET @FY = CASE 
        WHEN MONTH(@InputDate) >= 7 THEN CAST(YEAR(@InputDate) AS VARCHAR(4)) + '-' + CAST(YEAR(@InputDate) + 1 AS VARCHAR(4))
        ELSE CAST(YEAR(@InputDate) - 1 AS VARCHAR(4)) + '-' + CAST(YEAR(@InputDate) AS VARCHAR(4))
    END;

    RETURN @FY;
END;
GO

After executing the above query, the function has been created successfully.

get financial year from current date in sql

Step 2: Use the Function

SELECT dbo.GetFinancialYear(GETDATE()) AS CurrentFinancialYear;
How To Get Financial Year from Current Date in SQL Server

This approach makes your code cleaner and reusable across queries and reports.

Method 3: Using DATEADD and YEAR Functions for Financial Year Calculation

Another approach is DATEADD to shift the date and then extract the year.

Example: July 1 Fiscal Year Start

DECLARE @CurrentDate DATE = GETDATE();

SELECT 
    CAST(YEAR(DATEADD(MONTH, -6, @CurrentDate)) AS VARCHAR(4)) + '-' + CAST(YEAR(DATEADD(MONTH, 6, @CurrentDate)) AS VARCHAR(4)) AS FinancialYear;

How it works:

  • Add 6 months for the financial year end.
  • Subtract 6 months from the current date.
  • Extract the year from the shifted date for the financial year start.
How To Get Financial Year from Current Date in SQL

Method 5: Using FORMAT Function for Readable Financial Year Output

For better readability, you may want to format the financial year with a separator like a slash.

DECLARE @CurrentDate DATE = GETDATE();

SELECT 
    FORMAT(
        CASE 
            WHEN MONTH(@CurrentDate) >= 7 THEN YEAR(@CurrentDate)
            ELSE YEAR(@CurrentDate) - 1
        END, '0000') + '/' + 
    FORMAT(
        CASE 
            WHEN MONTH(@CurrentDate) >= 7 THEN YEAR(@CurrentDate) + 1
            ELSE YEAR(@CurrentDate)
        END, '00') AS FinancialYear;

After executing the above query, I got the expected output as shown in the screenshot below.

get financial year from date in sql server

Best Practices

  • Standardize Fiscal Year Logic: Use UDFs to avoid inconsistencies.
  • Parameterize Fiscal Year Start: Accommodate different fiscal year start months..
  • Use Proper Indexing: If filtering by financial year, consider indexing date columns for performance.

Conclusion

Calculating the financial year from the current date in SQL Server is a common requirement for organizations. Whether your fiscal year starts in July, October, or any other month, you can use SQL Server’s built-in functions like CASE, YEAR, MONTH, and DATEADD to dynamically determine the financial year.

You can try the methods demonstrated here in this article to achieve this requirements.

You may also like the following articles.