The Darkest Hour can go pretty dark if you're willing to take it there. It's Little House on the Prairie meets 28 Days Later. However, to really get good and dark with it, you should plan to run long. You can spend 6 or 7 hours on it and not slow down.
Cleansing of Black Spur is much more Classic in it's approach: "There's a ruined castle. Go see what's in it." It's a little more suited to a one-night game (if by "one night" we mean 4 to 5 hours).
The Vault is a dungeon crawl, if a reasonably brief one.
All three are in the Time of High Adventure.
Wyrmstone's got a big one that I never tire of suggesting: The Lost Prince. It's another that can go anywhere from four hours to three sessions if you want to do it that way, and it's got a nice bit of role-playing/character drama at the end that I've never heard complaints on.
And this summer's GenCon offering, Sins of the Fathers, is shaping up in a "murder mystery" vein. Or at least, I hope it is.