Whenever I get asked that question (OP), I start by differentiating between "scary" and "startling." Yes, I know these terms can be synonymous. But, for example, some movies are "scary" in a more traditional sense (like The Ring and the Paranormal Activity), some movies are suspenseful/startling (like The Village or Signs), and some movies are just gory (like the Hostel series). I wouldn't necessarily consider HHN to be "scary" for anyone that can differentiate fantasy from reality, which is why it's not good for children), but it can obviously be frightening.
Having attended HHN since its inception, it seems to me that for most adults the event is fun with both startling and gory elements. Unless a guest has a particular aversion to specific things like the sight of blood, clowns, and chainsaws, (i.e. phobias), there shouldn't be anything psychologically terrifying (scary) about the event. You will, of course, be startled countless times throughout the event. A clown standing in a well lit room probably wouldn't scare most adults, but a clown jumping out from behind a curtain in a dark room ought to at least startle most adults.
We have the benefit of YouTube, and there are countless videos available from the various HHN events. Take a look at them. That should give any potential guest a pretty good idea of the scary-ness level of the event. For the most part, what you see is what you get.
Or maybe I should say, what you don't see...