The last time I was really hyped for a game was probably The Sims 2. That's right, 2. I don't usually buy into hype all that much now. For me it often leads to disappointment, and poor purchasing habits. I don't get really overly excited about upcoming games anymore. But I remember back when GTA Vice City was coming out, the day it released I was in school. It was the longest day of my life, haha. In retrospect, it really wasn't all that good IMO. But that was back when the possibilities for new things in games were still very vast. There was a lot that could still in be done, in theory, when a game came out that we'd never seen before. Now games have pretty much done everything, at least that is how it seems to me.
I guess you could say I was mildly hyped about the release of Wet. The game charmed me somewhat, and I had followed it's development. I knew people were going to hate it. I bought it day one, and to this day, still have not played it, LOL! It was interesting to hear that the game was due to have a sequel, but was cancelled early on.
I think I tend to route for underdogs sometimes, games that I know people will tear to shreds because it's not Call of Duty, or Assassin's Creed, or Elder Scrolls, or Final Fantasy.