I am guessing your dates are in the following format: DD-MM-YYYY and you are looking to output them as YYYY/MM/DD? If so, you should be able to use the CONVERT() function to do this and not need to do substring and concat functions.
Lets say the
[date] value is '22-12-2020' (where this is December 22nd, 2020) and you want the date to be displayed as
By doing the following instead, you can greatly cut down the amount of code and processing required:
CONVERT(date,[date],103) as myDate
Which will output the date in the correct format for you.
Now the part that is likely tossing your error is if the date string is not in the correct format or is otherwise altered. What you might look at doing is something like:
SELECT CONVERT(date,[date],103) as myDate
WHERE [date] IS NOT NULL
AND LEN([date]) = 10
This way it will only gather results where the date field is not null and that it matches the expected length of the format - which should remove the error you are getting.
Now there are a ton of further conditions you can put into place, for example:
AND LEN(REPLACE([date],'-','')) = 8 which will verify it has the delimiters in it and the correct numbers. Or even
AND ISNUMERIC(REPLACE([date],'-','')) = 1 to verify it is only numbers and not letters. But the level of scrutiny you go to on your conditions will depend on how 'trashy' your data is. (I do not mean this in a harsh way, just simply that the cleaner your data is, then the less conditions you need to account for to decrease risk of failure.)