Seeing as its October, why not? Don't you just love when going through a fun game you spot something that is just..eerie..if not out right terrifying? It can be a horrific level, a odd room, or something implied.. The game Madou Monogatari (1) is a interesting rpg where you go through a scary tower full of rpg typical monsters as a kindergarten student practicing magic. Near the end of the game you see a terrifying cut scene where your classmates' faces melt and they scream in distress! Spoiler Luckily it's just a hallucination, however it's quite horrific to watch...
Sorceress Edea's parade in FFVIII immediately springs to mind. Creepy and dramatic ominous Latin chanting in the background, the entire city enthralled, and Sorceress Edea herself, who even brings stone gargoyles to life at one point. Not only that, but... Spoiler It ends with Squall taking a massive ice shard through his shoulder. Which maybe even kills him if you buy the "Squall is dead" theory. There's also the factory in NieR: Automata. Where hundreds of cultist robots attack you and commit suicide whilst "BECOME AS GODS!" is repeatedly screeched in the background, to the point that it becomes part of the music. Spoiler There is also a moment at the end of the game in the Tower where a bunch of suspiciously Manah-looking enemies multiply en masse, become too human, and then start killing each other. Then the door opens. It's a very disturbingly surreal experience... Also, in the original NieR... Spoiler Popola going INSANE right after you kill Devola. This is scary because she is the most passive character imaginable, and hearing her cackle like a lunatic and screaming "IT'S TOO LATE TO STOP!" is thoroughly disturbing. As a nice touch, the music changes from Song of Ancients - Fate (where they both sing) to Popola's own version, which plays in the more quiet parts of the village... Also pretty much all of Drakengard fits this. The entire game is messed up. The game's "non canon" endings are all nightmarish. Spoiler In one the Grotesqueries - giant babies with teeth - descend from on high and basically end the world...after devouring Arioch, an insane elf who eats children. Irony at its finest. In another Inuart revives Furiae - who previously committed suicide because her incestuos feelings for her brother were on full display - and she...came back wrong. That scream. Manah is also generally terrifying.
I was going to mention the parade in FFVIII, but was just beaten out. *ahem* Anyway, you want horrific? Watch a little kid sink into a poisonous sea of mud, calling out for his dead father to save him. That'll go and mess you up for awhile. Watch your step... (I'm talking about Tales of the Abyss)
A Hat in Time is supposedly a cute-as-heck platformer, with a sunny seaside town and a silly little chapter about bird movie directors, where you're an adorable kid with a big hat running around and being smug. And then, the Subcon Forest appears. Spoiler: SPOILERS FOR: THE SUBCON FOREST It all just feels "fake spooky" for a bit, with some ghosts, some fire foxes that dance around and say "We want to die! Yay!" and then you get to a certain level. Vanessa's Manor. It's literally almost like playing Granny. You run around her mansion trying to get a way out, and every single time she appears, you have to hide and make sure she doesn't see you. Vanessa appears as a red shadow with glowing red eyes, and every single time she shows up, the music changes to a terrifying tune. If you get caught by her, you are frozen to death, and Hat Kid even says "good bye, cruel world" if you die like that! It doesn't help that she's really speedy and is always crooning something along the lines of "come out, let's have some tea" or some other creepy taunt. "Cute as heck" for sure.