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 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 */
