SQL Query to Get a Financial Year Using a Given Date

In SQL, calculating the financial year from a given date is an important task, especially in regions where the fiscal year does not align with the calendar year. In this article, we will explain how to get the financial year from a given date in SQL Server, using simple and effective queries.

This guide will provide us with detailed steps on creating a table, inserting data, and executing queries to calculate the financial year based on the JoinDate column of a table.

How to Get a Financial Year Using a Given Date

For demonstration, let’s assume we have the departmenttable in our Geek's database. This table contains columns such as ID, SALARY, NAME, and JoinDate. We will calculate the financial year for each employee based on theirJoinDateusing the methods discussed earlier.

Example 1: Calculate the Current Financial Year

Now, let's look at how to calculate the financial year for the current date. We will use the GETDATE() function to get the current date and then calculate the financial year based on it. The calculation considers that the financial year starts from April 1st and ends on March 31st of the following year.

Query:

DECLARE @FIYear VARCHAR(20)    
  SELECT @FIYear = (CASE WHEN (MONTH(GETDATE()))
  <= 3 THEN convert(varchar(4), YEAR(GETDATE())-1) + '-' + convert(varchar(4), YEAR(GETDATE())%100)
  ELSE convert(varchar(4),YEAR(GETDATE()))+ '-' + convert(varchar(4),(YEAR(GETDATE())%100)+1)END)    
  SELECT @FIYear AS F_YEAR    

Output

Explanation:

• This query checks if the current month is before or after March (the start of the fiscal year in some regions).
  
• If the month is before or in March, the fiscal year will be the previous year; otherwise, it will be the current year.

Example 2: Get Financial Year Based on a Given Date in the Table

Next, let's calculate the financial year for each record in the departmenttable using the JoinDatecolumn. This example demonstrates how to compute the financial year based on the employee's joining date, using the same logic we applied for the current date in Example 1.

Query:

SELECT  
  (CASE 
    WHEN (MONTH(JoinDate)) <= 3 
    THEN CONVERT(VARCHAR(4), YEAR(JoinDate) - 1) + '-' + CONVERT(VARCHAR(4), YEAR(JoinDate) % 100)
    ELSE CONVERT(VARCHAR(4), YEAR(JoinDate)) + '-' + CONVERT(VARCHAR(4), (YEAR(JoinDate) % 100) + 1) 
  END) AS FinancialYear,
  * 
FROM [dbo].[department];

Output:

Explanation:

• This query calculates the financial year for each employee based on the JoinDate column.
  
• It uses the same logic as the previous query but applies it to each employee's JoinDate to calculate their individual financial year.

Conclusion

Calculating the financial year in SQL is a common requirement, especially when dealing with financial data or fiscal year reports. Using SQL queries with the CASE statement, YEAR(), and MONTH() functions, we can easily calculate the financial year based on a given date. This article demonstrated how to use these functions effectively to get the financial year from both the current date and a date column in a table.