I have a few moments I that I will treasure forever:
The first time I played Mario 64. I hadnt owned a SNES, so you can imagine how much in awe I was when I went from NES graphics to N64 graphics. I was in Wal Mart playing the demo, and it took a hold on me. It would be some time until I got an N64 for myself, but I finally did and I can remember playing Mario 64 at home...great times.
I also remember the day I got OoT. It was a christmas gift, and as soon as I opened it (it was my first gift) I ran into my bed room and put it in and started playing it, leaving all my other presents there. So if you watch the home video from that christmas, it shows a little kid in PJ's in a rocking chair, then a second later all you see is a chair rocking with no one in it.
That whole game was like one huge magical moment though. I felt so said when Link had to leave Sara, and I actually had a crush on Zelda when I first saw her, and I could hear Zelda's Lullaby in my sleep. I remember meeting the Zora's and the Gorons, fighting to save the hand of my fair lady, and returning to the lost woods as a man to meet my friend of childhood.
There are other games that deserve an honorable mention...Star Fox 64, Pokemon Red, Mario Bros., Final Fantasy Tactics, and Xenosaga.