Genesis/Sega CD: Golden Axe, Phantasy Star 2, Shadow Dancer, Strider, Sonic the Hedgehog, Streets of Rage 1 & 2, Gunstar Heros, Castlevania Bloodlines / Willy Beamish, Road Avenger, Lunar, Popful Mail, FInal Fight CD (So much better than the SNES cart, basically an Arcade perfect port.) Snatcher, Sonic CD.

Final Fantasy VI
Super Mario RPG
Donkey Kong Country 2
Super Mario 64
Final Fantasy: Crystal Chronicles
Metroid Prime
Baten Kaitos
Baten Kaitos Origins
Xenoblade Chronicles

Probably Secret of Mana for me. A JRPG that had incredibly co-op gameplay? Yes please, thank you. My brother and I spent countless hours on that game when it was released. I was a very young lad at the time but I still remember how much fun it was. Earthbound was a gem too.

PS2 era? Final Fantasy X and Kingdom Hearts. Both games I had no urge to see how the story wrapped up. Just wanted it to go on and on. I remember playing Kingdom Hearts for the first time, before everything was on the internet, and going from world to world so genuinely excited to see where you'd be going next.

The Last of Us on PS3 was also an incredible experience. Picking through the rubble relentlessly trying to find some resources to make a shiv, because you know you're vulnerable without one. You really do experience what they're going through. The immersion is incredible. Well done Naughty Dog.

My top 10 favorite games of all time

1. Zelda: Ocarina of Time (N64)
2. Super Mario 64 (N64)
3. Goldeneye 007 (N64)
4. Resident Evil 2 (N64)
5. Resident Evil 4 (GC)
6. Resident Evil Code Veronica (DC)
7. Mario Kart 64 (N64)
8. Parasite Eve (PS1)
9. Final Fantasy VIII (PS1)
10. Final Fantasy VII (PS1)

This is a tough one to narrow down. I will just list a few in no particular order. *edit - Note, as someone said that people are listing Favorite games. This is not a list of my favorite games but a list of games that when I played for the first time I had an overwhelming sense of wonder and joy. The games that stir memories of glee and delight and never before experienced sensations. All these games are magic for that reason.

Wonder Boy III - Sega Master System. This game blew me away with how cool it was and how you had to solve puzzles by changing your shape.
Final Fantasy (original) - NES. This is still one of my all time favorite games and I go back and play at every other year or so.
Super Mario Bros. 3: NES. No explanation needed.
Ascendancy - PC. An all around fantastic strategy game about space exploration and galactic domination.
Mutant League Football - Sega Genesis. It took what was great about the Madden games and threw in comical violence.
Counter Strike 1.5-Source - PC. Still one of the best FPS series out there.
Quake 3 - PC. This was one of my first multiplayer shooters. It was also the first game I joined a clan in. Instagib mod FTW, and trick jumping.
Final Fantasy Tactics - Playstation. Best tactics game I have ever played and still better than any I have tried to date.
All Final Fantasy games 2-7. NES, SNES and Playstation.
Final Fantasy X - PS2. One of my favorite RPGs
Uncharted series - Playstation 3. Nothing need be said.
Skyrim - PC. Best First Person RPG out there I sunk more than 120 hours in this game to complete every single missions available including expansions. I am still wowed at how good this game is.
Battlefield 2 and 4 -PC. 64 players at once + commander. Most realistic bullet physics available in game (aside from Americas Army). Every game is all out chaos.

Quick note of games I forgot. Metal Gear Solid, Metal Gear Solid 4, Tekken series, Altered Beast, and in the Arcade realm, TMNT/Simpsons/X-Men 4 player games and the pinball game Medevil Madness. *edit: someoen just reminded me of Sonic the Hedgehog and SuperMario RPG for the SNES--these are now on the list.

That's my "Short" list.

I remember the 1st time I fired up Sonic The Hedgehog back on the Genesis..........
OMFG !!!!!! It was like seeing a mountain load of toys under your xmas tree........

Not quite sure what you're asking.

You seem to be talking about which game you were most hyped for, but everyone is just listing their favourite games now.

I usually had one of these awe-inspiring gaming experiences per generation:

NES - Super Mario Brother 3, the game just blew me away with its technical achievement, length, fun and variety. I actually got this game BEFORE I got an NES.

SNES - Link to the Past. I remember seeing previews for this game in Nintendo Power and getting super hyped for it. Then it came out and just pulled me into the world. It remains my favorite video game of all time

N64 - Super Mario 64. I first played this game at a kiosk in a game store and was amazed. I hadn't been following the 64 development too much, so it was kind of like a game bomb dropped on me. OoT was amazing as well, but that answer is cliche, and it didn't have quite the impact of that first glorious SM64 experience.

PS2 - GTA 3. This game solidified my love for open world games, and I still think it remains as one of the best GTA games made. I have probably 100% it 5 or 6 times and would happily go back and do it again.

Gamecube - Metroid Prime. This game was unbelievable. I was skeptical because I despise FPS in general, but this game was so much more.

Wii - Wii sports, because in a growing world of repetitive, mundane game play, it introduced a new gameplay experience for a long time gamer.

Wii U - Lego City Undercover. And odd choice but I just loved everything about this game. It was so charming, fun and a truly excellent and fresh take on the open world genre, which has jumped the shark a bit as of late.

EDIT:  It is kinda funny that you used the term "magic" as that is exactly how I described my first experience with Mario 64 to my girlfriend who had never played it.  She is (some what strugleingly) playing through it now

Some of most magical games I've played include BioShock, Ico, Metal Gear Solid 3, Metroid Prime, Half-Life 2, Banjo-Kazooie, Super Mario 64, Psychonauts, and pretty much every Zelda game.

Last addition to this list, I swear. The original Zelda for the NES