SM3 is very much becoming the standard. Its an effect the latest generation of consoles will all support, too - so we should be seeing even more of it in the future. However, few games will really NEED it, its just that particular games -- like Doom 3 and Splinter Cell chaos theory - are pretty much designed around it. Splinter Cell in particular had support for SM1 and SM3, but NOT SM2 on its release, so it really wouldn't have looked great on a x800.
That being said, the x800's are the most powerful cards for the money - they would leave the 6800gt way behind in non-sm3 titles like Half Life 2. But still, HL2 is the only game out of the last five I have purchased that doesn't feature and benefit a great deal from SM3...
If you play racing games and sports sims, you'll get away with it. Otherwise, if you like 1st person shooters and moody adventure games you should really get a 6800gt. The 'darkness' is becoming too much of a gaming feature -many of these sorts of games utilise sm3 for tactical/stealth purposes as much as visuals.