My HD cable box upconverts all signals to 720p. They don't look that bad to me. Not nearly as good as, say, a 30" 4:3 SD Sony CRT, of course, but it's perfectly watchable. Looks as good as any other fixed-pixel display (DLP, LCD, etc).
480i and composite input look pretty horrible for some reason, but that's my particular TV (a Maxent plasma).