AH so the explanation currently given, given, while probably a valid fact (the return would be truncated in that instance) has nothing to do with the behavior we are seeing, which is caused instead by the ANSI standards having to do with using a starting position that is less than one.

Doing something like SELECT SUBSTRING('123456', 2, 8) would invoke the truncation based on Start + Length-1 is > (length of expression). Understandably in that case you are not going to get back 8 characters, even though you said that is what you want, because it's not there to return to you. (I presume the alternative to truncating would be something like padding out with spaces? who knows.)