Clock Tower for the SNES is still the scariest game ever.

Is it still scary if you would play it in this day and age?

I'd say that it would be a tie between Silent Hill 2 and Fatal Frame 2: Crimson Butterfly for me. Silent Hill 2 for meandering throughout the worst parts of your psyche, scaring you due to the atmosphere never letting go of any tension (it doesn't even need enemies on screen to terrify oneself). Fatal Frame 2 for combining jump scares, atmospheric horror, and spirit-related horror in bone-chilling ways. Most of both of these series are insanely scary (let's forget about Silent Hill being scary at all after 4), but in both series, the second game seems to be the paramount of their frightening capabilities.

I only ever played it via translation on an emulator.

Wow, I thought I would be the only one to answer Condemned. I was on edge the entire time I played that game.

To me the "Penumbra" games (Mainly Penumbra Overture) are so scary that at one point I had to pause the game and run out of the room out of sheer horror and it takes a lot to make ME do that (I watch a lot of horror films but find most of them not that scary)

I have not tried the new game "Amnesia: The Dark Descent" but I hear it's just as scary.

Other then a few games that gave me jump scares (FEAR, Dead Space, STALKER) that's about it

When I first dove into Silent Hill 2 I was using a pirated PAL ISO on my NTSC PS2, which made it even scarier. It was in black and white (something I thought was actually part of the game and not a formatting bug) and everything was darker than it should be. When I was wandering the halls in that hotel, if I wasn't in a lighted area the screen was PITCH BLACK and enemies were really difficult to see (but still very easy to hear) and so that set the tension even higher

Creepy? Yes. Scary? Not by a longshot.

+1 to fatal frame 2 and Silent Hill 2

Mogsworth pretty much say why this is very scary, but if I might add the game play using camera obscura too exorcise the ghost is another element that make this games/series even scarier. Well maybe the slow movement of the character is kind of annoying but you'll get used to it.
As for Silent Hill 2 I've also got the pirated PAL ISO, it's funny how a black and white picture can really boost the scariness of the game.Btw Fatal Frame 3 also used this