Conversion failed when converting date and/or time from character string while converting nvarchar to date

Why not just use convert with the correct (120) format?

update usda_losses_temp set [end] =  convert(date, hazard_end_date, 120);

See dbfiddle

If thats not working then you have some invalid dates for the format provided within your data.

Your sample date, '2020-09-31', can't be converted to a date, regardless of what kind of formatting or substrings you apply to it. September only has 30 days.

Thus revealing the underlying problem: These never should have been stored as strings in the first place.

To find the offending rows:

SELECT * FROM usda_losses_temp WHERE ISDATE(hazard_end_date) = 0;

Once you've fixed your bad data, you should use the simple CONVERT, col, 120) syntax Dale suggested, instead of all this messy substring goop.

And add a check constraint (CHECK (ISDATE(hazard_end_date)) = 1) until you or they can fix the table. Because that is the real problem here.