FPS or other linear action games: as bad as it sounds, between six and ten hours is usually preferable to longer. Unless you have enough content to spread around, that's about when fatigue sets in. Maybe if they went back to the Episode structure of early games like Doom 1 or Duke Nukem (three or four episodes of eight levels each), but games like Doom 2 and Unreal just droned on and on, with not nearly enough locations, enemies, and weapons to make the entire game enjoyable.
Sandbox games: Finishing up the main missions and story driven side quests should take 20-40 hours. If it goes on too long, I find I lose the thread of the story, which is usually weak enough. The beauty of such games is that they encourage you to fap about by making it loads of fun to do so.
RPG: I'm just starting to get into these again with Fallout and Fable. Fable is really closer to a sandbox game and has about the right amount of content. Fallout probably ends up having way too much content in relation to its story, but I could definitely go for more story driven quests in it. But I really don't have enough of an opinion on RPGs to say what's a proper amount... but over 40 is better than under.