In answer to the original question, yes I believe it is hype, at least to an extent.
As I've said on here before I went went from CRT 28" to 26" HDTV. Now, the difference there is a wow factor, no doubt about it. However, one downside is on the CRT, games feel cinematic in quality. COD2 looked and felt like an interactive saving private ryan. On the LCD set it suddenly looked like a computer game again (a good looking one mind).
Now, when playing around with the settings I found that some games definitely ran better in 480P than 720p. NFSMW demo was a prime example. in 720p it was jerky vision but in 480p is was much much smoother. COD2 suffers slightly in 720p when theres loads going on but doesnt in 480p. The strange thing was the games didnt look much diferent, leading me to believe the wow factor comes from the difference between LCD and CRT technology rather than the actual number of pixels.
Because of this I decided to try an SD plasma, as whilst the LCD was good on the XBOX it was frankly pants as an allround TV, and I do like my movies so wanted to try a bigger screen. So I sent it back and bought a 42" SD plasma.
The picture on this is stunning - crisp lines, vivid colours and best of all no frame rate drops! Mates have been round and their jaws have dropped watching PGR3 and COD2 on it, and they were convinced it must be in some sort of HD mode. Its 100x better than an original xbox which I also run into the set at 480p through component. The xbox is better than it was on the TV, but the lack of detail and jaggies (no AA compared to the 360) is very apparent. I'm just saying this as I know some people have questioned if the 360 actually looks like an xbox 1 at sd resolutions. Best of all though DVDs and even SKY look fantastic as well!
So in summary I'd say that HD is laregly hype unless you use your 360 like a pc (ie sat with your face 2ft from the screen). Most people are getting the wow factor because they are moving from CRT blurriness to LCD crispness. If anyone else wants to test it, set your 360 from 720p to 480p and play PGR3 or something and see how different it actually looks in game. I remember someone posting a PGR screen showing the textured door trim on a 355 and people saying "wow, thats just not there on SD" but the fact is it is - its perfectly visible on my plasma in SD, but it wasnt on the CRT so its more about image quality than number of pixels.
edit - just to be clear - If I could afford a HD 42" plasma I would still get one. HD is better, but its very subtle compared to the leap from CRT to LCD/Plasma image crispness/quality