SQL – How to select current financial year from lookup table

The following SQL shows how to select a row from a table using the current financial year as the WHERE condition.

This is useful if you have a table of years and need to get the id corresponding to the current financial year, for example if you had the following data and needed to get the ID of the current financial year:

Year_ID Short_Name
75 2015/2016
76 2016/2017


This assumes that the financial year for your country is 1 July – 30 June.

This SQL query uses

  • GETDATE to get the current date
  • MONTH to turn the date into the month number (e.g. 5)
  • YEAR to turn the date into the year number (e.g. 2017)
  • CAST to convert the number to a string (which makes the comparison to the lookup table possible)

The WHERE condition checks what the current month is and creates the current financial year as a string – it then compares it against the table and selects the first (TOP 1) row.

SELECT TOP 1 TY.Year_ID, TY.Short_Name 
FROM T_Years TY 
( MONTH( GETDATE() ) <= 6 AND TY.Short_Name = CAST( YEAR(GETDATE() ) - 1 AS varchar(4) ) + '/' + CAST( YEAR(GETDATE()) AS varchar(4) ) ) /* 2016/2017 */
( MONTH( GETDATE() ) > 7 AND TY.Short_Name = CAST( YEAR(GETDATE() ) AS varchar(4) ) + '/' + CAST( YEAR(GETDATE()) + 1 AS varchar(4) ) )  /* 2017/2018 */

Related Articles

Leave a Reply

Your email address will not be published.