For me, it was the first time I ever played a video game, and I'm surprised I remember it. I was 3, and I don't remember much fom that period in my life (I have fallen on my head multiple times since then). I played the original Super Mario Bros. I just remeber it being the coolest thing in the universe!!!
And then there's Chrono Trigger, when Crono dies. swear, that was one of the saddest moments in my life. Kinda. I mean, I thought he was never coming back, and I played the rest of the night until I brought him back, which was consequently at dawn, much like in the game.
Finally, my last moment was much, much more recent. In fact, I experienced it, yesterday. It was at the end of Megaman Battle Network 3 when Megaman.EXE selfdestructs himself in order to say Lan. And I really wish more people would realize that that game is NOT A KIDDIE GAME. That just makes me mad when people say that.
Anyway, that's my 2 cents.