Date CAST in WHERE clause does not return items

Question

I have a problem with SQL query that has a CAST function in it, so this is my condition:

CASE
  WHEN FYStartMonths = 1 
  THEN 
    'Period ' + CAST((MONTH(EndDate) + 1) / 2 AS VARCHAR) + ' ' + CAST(YEAR(EndDate) AS VARCHAR)
  ELSE
    'Period ' + CAST((MONTH(EndDate) + 1) / 2 AS VARCHAR) + ' ' + CAST(YEAR(EndDate) AS VARCHAR) + ' - ' + CAST(YEAR(DATEADD(YEAR, 1, EndDate)) AS VARCHAR)
END AS FormattedFilingPeriod

So the results of this is something like this

Period 3 2022 - 2023

And the SELECT clause looks like this:

SELECT *
FROM ActiveItems
WHERE FormattedFilingPeriod LIKE '%' + 'Period 3 2022 - 2023' + '%'

The problem is when I'm adding a WHERE clause under the SELECT it just does not return the items if I specify Period 3 2022 - 2023, it returns items only when I'm specifying something partialy like Period or 2022 - 2023, but f.e. 3 2022 - 2023 will not return anything. I have no idea why this happening so I would be glad to hear your thoughts.

I've tried to overwrite case conditions with the format, changed casting to AS VARCHAR(MAX) like was mentioned in this question SQL Cast works in Select but not in Where clause but this doesn't work for me.

Answer 1

The logic for the WHERE clause in the question should be equivalent to this:

WHERE FYStartMonths = 1
    AND EndDate >= DateFromParts(2022, 5, 1) AND EndDate < DateFromParts(2022, 6, 1) 

Writing the WHERE clause like this instead will perform massively better, because you can still use indexes on the EndDate column and you're no longer doing conversion operations on every row in the table.

Additionally, this should make the fix for the issue in the question more obvious. The question isn't entirely clear, but it seems like the problem is the value in the FormattedFilingPeriod column won't match when FYStartMonths = 1, because the Period text is missing. Writing the WHERE clause this way makes it easy to just remove that condition.

But it also looks a little like this is based on a search filter, where a user might enter anything or select certain pre-formatted queries. In that case, you're still MUCH better off writing front-end code to parse those queries so they can be translated in SQL WHERE clause expressions as above.