Zelda is kinda the opposite for me, I always think I won't enjoy the game. For some reason, I tend to remember things that annoyed me about the games rather than what I enjoyed, but once I start playing it's like, Whoa, half the day has passed, I really need to go to the bathroom, maybe think about eating...
Phantasy Star 2 is more a game I wish I could still love, but don't. I love the memory of finishing that game once upon a time, but it's so tough to play it much at all now. I manage to crawl out of the first few dungeons, beaten and torn and it's nice to see I still have the skill and endurance, but the antiquated gameplay just gets to be too much.
The Elder Scrolls games definitely fit the bill for this topic though. I love to mod the hell out of Oblivion, but once I start actually playing I just can't give two teeny tiny ****s about any of the story or quests. I love New Vegas and FO3 though, so it's not that I just don't dig that style.