It really depends on how far away you sit from your TV, what the screen size is, and how good your vision is. When I'm sitting three feet from my 28" HD monitor, you bet I can tell the difference between HD and SD. My vision's not quite 20:20, though, so on the couch 15' away from a 100" projector screen, I'd have a hard time telling 720p from 1080p just based on sharpness, but the larger size of any macroblock artifacts would give it away.
In the right conditions, I'm positive you'd see the difference, too. Try renting both a DVD and Blu-ray of the same movie from Redbox, and watching them a little closer to the screen. The Blu-ray will let you see more individual hairs, individual distant bricks, etc. But even SD on an HD screen can be better than SD on an SD screen, due to increased pixel density and reduced flicker.
Vision clarity definitely plays the part. And so does how far you are from your TV. I am about 2.5 screen lengths away from the screen and the point is, it isn't that much better that it warrants all the inconveniences that come with watching an HD.
It requires another player. (In case of BluRay)
It's harder to stream through the internet. (In case of digital formats)
For those people, like me, who in 95% of practical uses do not see the difference, the above disadvantages make it a no brainer.
I guess I am asking, is it really worth it all the trouble?
If you consume HD content the "legitimate" way by inserting a disc into a Blu-ray player, then no, I don't think it is. You have to put up with 7 minutes of unskippable previews in some cases, player updates that take two hours to download and install, two minute load times on two-year-old players, etc.
If you front-load the inconvenience by buying the discs, ripping them all to a hard drive, and using something like XBMC or Plex on an HTPC, then for any serious watching where you sit down for the whole show, yes, it absolutely is worth it. I wish more people had access to the convenience a media server gives; it's what content should be like in the digital age.
Edit: I'm usually about 1.5 screen lengths away, so that probably makes a difference.
I would be careful with this comparison. Many BluRay disks of older movies are just upscaled from the DVD (I'm guessing here - not actually sure how it's mastered) and aren't significantly different than what you would get with an upscaling DVD player.
You would have to get a "recent" movie mastered specifically for BluRay to see the difference. I've done this several times myself (specifically "The Princess Bride" - which appears to be an upscale, and "How to Train your Dragon" which is significantly clearer).