It differs somewhat by the type of game. My current project is going to clock in at about 2 hours of play time in the main adventure (compare with literally thousands of hours spent making it), with the potential for a lot of replayability. I initially aimed for a longer adventure, but found that to keep things at the level of novelty, detail and quality that I think players deserve, it's not worthwhile to make the game too long. It's better to sacrifice length than to sacrifice quality.
One of that game's core qualities, though, is that everything is a new experience fundamentally different than what came before. Not all games need this. For a game where a lot of content equates to variations on the same theme, and more content doesn't require building it all from the ground up, I think anywhere from 5 to 20 hours is a good target, ideally somwhere in the middle of that range. Below 5 hours, it can be hard to tell a complete story (although great storytellers can do it in less than 5 and heavily gameplay-focused games don't need to tell a big story anyhow). Beyond 20 hours, you're likely to wear on your player's patience unless your storytelling or gameplay are incredibly compelling.