I've been a Diamond now for several months. No tangible impact on my revenue stays--same kinds of upgrades, same kinds of treatment as when a Gold. However, as NeverAtHome wrote, it's all about reward stays: the Diamond desk is squeezing me into a property where there would otherwise be no rooms available from HHonors points usage. Terrific!
(If you're into better treatment and upgrades, then Starwood's Platinum is MUCH better than their Gold.)