When done right, the results are pretty nice. I can generally tell the difference between my HDCDs and non-HDCDs. But I do also have some regular CDs that, if I listening to for the first time and didn't know otherwise, I'd probably call HDCD, and vice versa I've got one or two HDCDs that I'd never guess were encoded differently. But more often than not, the difference is a nice improvement.
As for the future of it, who knows. A few observations though: first, that even though the A/D converter's been discontinued, they're not suddenly ceasing to exist, and there are still producers around who like to use it, even if it'll never take ordinary Redbook's place. Plus, there are a lot of HDCDs already in existance and no shortage of quality players that can decode them (and I've never heard of a player that couldn't at least play the Redbook), so even if it died out this very second, it'd be a long time before they disappeared from the market.
Even if it's not always a mind-blowing, jaw-dropping, you-haven't-heard-music-until-you've-heard-this technology, you'll rarely put an album in and wish it hadn't been HDCD encoded (unless it's made by people who don't know how to do it, and then odds are that's not the only thing they've done wrong). I'm happy I opted for a CDP that can read it.