Honestly the only games I've ever picked up and put time into, without actually finishing, are Final Fantasy 13 and Halo 2. FF13 was an absolutely painful slog, with a combat system designed by people who didn't want a turn based system, but couldn't think of anything better. Between that and a weak story, I stopped playing before those end credits rolled. Halo 2 on Legendary was cheap. So very cheap. After nearly snapping a controller in half I decided it was time to decrease the difficulty.
A few games that I've dropped mid way through are Dragon Quest on the GBA (can't remember the number), Guildwars 2 (do MMOs count?), and Sword of the Stars: The Pit. All of them for the same reason, you grind and grind but get nowhere. 300 hours of content isn't interesting if 290 of it is grinding.
I've finished the big ones. Metal Arms: Glitch in the System, Skyrim, System Shock 2, and even completed everything in FFVII (grinding on that submarine absolutely blew chunks). I tend to have one game active at a time, and move on whenever it's been beaten.