Wind Waker is probably the best example of this in existence. And to think the art style was such a big detractor for so many people. Twilight Princess looked more dated on THE DAY IT WAS RELEASED FFS.
A lot of SNES games come to mind. Good to see Chrono Trigger has already been mentioned, it was the first one that I thought of, actually. Just great sprites and an awesome art style. One that still blows my mind is Super Mario World 2: Yoshi's Island. Like wait, SNES was capable of that?? The Sonic games still look kinda nice, even if some of the art direction was, well, on the tacky side.
From the N64/PS1 era not that much really jumps out at me. Super Mario 64, I guess. It's so colorful and simple, but it is starting to look a little blocky. The only on I can think of that really impresses me in retrospect is Conker's Bad Fur Day. Synched voice acting? In an N64 game? And the characters have individual fingers? And their eyes actually focus on things? That game really curb-stomped everything else of that generation on a technical level. Perfect Dark had some really impressive stuff mixed with a lot of butt ugly stuff. Like, half the face models looked incredibly real, the others were big blurry blobs. Most of the guns still look great today, except the alien guns look like complete shit.
PS2/Xbox, I think have been covered pretty well in previous posts: Metal Gear Solid 2&3, SoTC & Ico, Resident Evil 4, and lots of the cel-shaded stuff.
Nintendo is really good about this, usually. I see Mario Galaxy, Skyward Sword and SSBB still looking awesome ten years from now.