Thanks for your responses. I agree regarding the DateTime over varchar but unfortunately, it is a school management information system provided by a company so we have no control over the table or column formats.
This query is embedded in a SSRS report and produces the parameters for the report to run. In this case if the query was run today it would produce the parameters as shown below which is the start date and end date of the current term (row 1 of the screenshot in the original post).
The master report then looks for detentions and merits etc which have been inputted during the school term that the report is run via these parameters.
The SSRS subscription is disabling itself if the report/query runs during a school holiday period between the dates of 2 terms and a term cannot be found and therefore errors. For example the Christmas holiday which takes place between term 1 and 2 of the table in the original post (2021-12-11 - 2022-01-05). Annoying there is nowhere in the database that stores holiday information and the web-based front end application is coded to treat the dates in between this as a holiday period.
I guess one solution would be to pull the start-date and end-date as normal but if a term cannot be found could it provide a start_date which is actually the end date of the previous term and an end_date which is actually the start date of the next term.
It doesn't matter if the SSRS subscription produced is blank.
In an ideal world we would be able to only run the SSRS subscription on dates that are during the school term but the subscription schedule options are not that advanced.