The only game I can think of right now is Resident Evil 4, but I'm sure there were others.
There were two parts in the game that I was absolutely terrified at. The first one had to be the hedge maze at the castle. I swear, any time I heard some sort of noise, I would run my *** of to the entrance and sit there with a shotgun.
The second had to be the fight with "It". I wouldn't even enter the area to start the fight for a while.
One part that really made me jump is when you are going back to the church in the beginning to get Ashley. It's raining, and you're running up the path. You can't see too much, then BAM. Lightning strikes, and there's 3 wolfs right in front of you. I jumped so high at that moment.
OH, DUH. DOOM 3.
I never played it at night. When I played it, I would have my door open, windows/blinds open, and no dark spots in my room. And even then, I was going very slowly though the game.
Playing Co-Op on Live was a different story though, I wasn't scared or anything.