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_IDShort_Name
752015/2016
762016/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 
WHERE 
( MONTH( GETDATE() ) <= 6 AND TY.Short_Name = CAST( YEAR(GETDATE() ) - 1 AS varchar(4) ) + '/' + CAST( YEAR(GETDATE()) AS varchar(4) ) ) /* 2016/2017 */
OR 
( MONTH( GETDATE() ) > 7 AND TY.Short_Name = CAST( YEAR(GETDATE() ) AS varchar(4) ) + '/' + CAST( YEAR(GETDATE()) + 1 AS varchar(4) ) )  /* 2017/2018 */

Was this article helpful?

Related Articles