Sven Coop. That game is soooooo much fun on LAN. I'm not even kidding. Counter-strike is a good one, too.

Single player, though, hmm. I played Xenogears a lot when I first got it - well into the night when I got it, then practically all the day after. I played Metroid: Zero Mission a good bit into the night, too. Generally, I'm more of the sort to play things from early in the day all the way through until midnight, then sleep and start the next day. I don't get caught up enough that I stay up all night playing a game.