I'll agree that RPGs will probably take a while. Dragon quest and Final Fantasy games take a good number of hours to beat, and then you can usually do more if you want to get the secret stuff. I haven't played Chrono Trigger, but it would probably take a while to beat as well.
Castlevania games often take a while as well. They are 2d action adventure games, similar to Super Metroid, if you haven't played any of them before.
Zelda, of course.
Advance Wars games will take a long time to beat. There are a few of them for DS at this point, and each mission can take a while.
Harvest Moon games are kind of a niche game, but if you like them you can spend forever playing them. Rune Factory I/II are closer to traditional gaming, but Island of Happiness was fun for me as well. Stay away from Harvest Moon DS.
I'm playing Soma Bringer right now, and it seems like it would be a long game. Grab the translation patch and have at it. The translation is like it was a completely localized game.
Some other games that may be long-ish, though I haven't played them personally:
Summon Night - Twin Age
The World Ends With You
Lock's Quest
Kirby Super Star Ultra (not an exceptionally long game, but for some reason I play it over and over)
Mega Man ZX (medium length)
Mega Man Star Force (I have no idea about the quality of this game, but the battle net series on GBA was both good and long)