You could make it more succinct by using LEFT, but it's probably as efficient as you're going to get. Are you suffering performance issues with the ORDER BY then?

You have an error there at least, unexpected EOF (End Of File), so it's not quite the same.

Anything in the error file?

You don't set it, it's a limitation. The maximum row size within SQL Server is 8060 bytes. Although you might have more per row if data for a varchar is...

There isn't a limit no, but with that many columns you may reach the limit of SQL Server for the maximum size per row.

Sorry for the "on topic" question, but my Google-fu is failing me, and some of you are (very likely) more informed on such matter than I am. Has anyone seen...

SQL Server has a FORMAT function, so if you were doing this with a scalar value you could use:
SELECT FORMAT(GETDATE(),'hh tt');
I don't, however, recommend applying that...

One method is to use CHARINDEX. Performance on a large dataset will (probably) not be very good, however. Doing lots of pattern matching on a string in SQL...