I can't imagine an Elder Scrolls game without a day/night cycle, even though the primary change is locked doors and different insects. The NPCs having schedules beyond walking the same area perpetually, bumping into things, adds character to the world that I often don't think about, but I'd miss it if it went away. Then there are games along the lines of Majora's Mask (not exactly an RPG) where the passage of time and the NPC schedules are core elements of the gameplay. Even if it's solely or largely for atmosphere, it's fine in games like that as long as you can fast-forward the cycle, and there's always something to do.
On the other hand, you have games like Shenmue (which I never finished), where you literally have to wait while the in-game clock advances. The consensus there seems to be that time was a good component to introduce, but forcing players to watch paint dry while it elapsed was a daft idea. I guess they added in a "wait" option for the sequel, which would help. I don't know that it would completely fix it, though. I've played a number of indie games the last few years that try to have scheduling and time as core mechanics, and you inevitably end up at a point where everything is happening at around the same time on the same weekday. You either have to waste time across several weeks to do everything, or use a "wait" feature to skip ahead. I think that's the worst possible example of using time as a mechanic. It's just there exclusively because it seems realistic to the maker. And why wouldn't an NPC only pop up at the lake one particular Saturday in spring?
In JRPGs I've mainly seen it used for the Zelda-like feature where different enemies show up at night, or certain places are only dangerous or more dangerous at night. That can be fine, but it's something I usually find a little tedious. It's also the sort of feature that developers sometimes seem to lose enthusiasm for as the game goes on. I've played a few games where the first area had nearly all the enemies replaced when you went out at night, and then towards the end, it was one or two replacements, and one might only be a palette swap or a buffed version of an enemy that shows up during the day. They tend to couple this with rearranging the more-or-less static NPCs in town, or having "shady characters" appear, and that always makes me sigh a bit. JRPGs have always had this thing where you need to talk with hundreds of unnamed, uninteresting people on the off chance one of them has something useful to say, or somehow gives you the rusty iron stick that will unlock the ultimate sword 50 hours down the line. Multiplying that by changing how people behave during the day, or adding new characters at different times, makes it a little much for me.
I just got Dragon Quest XI this weekend, and it has that sort of JRPG day/night cycle. The first place I tried it, the only thing I noticed was that the bats (drakees) showed up more frequently at night. Everything else was the same. So it seems like a bit of a bolt-on that doesn't serve much purpose thus far. I'm not sure if the towns change yet. I'm also not sure if there's anything to other times of day. It seems like there are natural transitions, based on play-time—at least that's what I gathered from the camera suddenly pointing up into the floor of the bridge I was under, and the sky suddenly going dark—but when you rest to pass time there are options for dawn, noon, dusk, and night. As soon as I saw that, I thought, "I hope this isn't going to be total bullshit", because I was envisioning having to search around the map at all four times, trying to find the one enemy that drops blue pearls or whatever, so I can make keys to open chests or something. That was before I saw that time progressed naturally, if rather abruptly, so hopefully it won't live up to my worst fears.