you probably have one or more rows in the table where the data in your column does not match the pattern you have described.
A value of '' does that for me. Interestingly this value casts to an integer value of zero, but won't cast to a numeric, giving the same message you have described...
That's what I originally thought myself so I did some investigation into the data that was being queried and there does not appear to be any problems there.
I then thought it might be due to the ORDER by clause but i have dismissed that as well as that should only happen after the base data has been grabbed (unlike a WHERE clause).
But I think I just figured out what is actually causing the issue.
The query I am performing spans multiple tables with criteria for the where clause spanning across them. I think the query is starting with a query across the FutureMaturity data table and then linking to the main query. So when it is doing the CAST it is not being performed across the subset of data I thought it was it is actually going across a larger cross section of data.
Now all I need to do is remember how to get a query plan for a stored proc and I will be able to confirm my suspicions.