Aside from the gameplay issues, there is also an emotional and aesthetic result of having day/night cycles. My most memorable moment from Neverwinter Nights II was walking up the trail to the HighCliff ruins while watching the moon cross the starry sky and reflect from the ripples in the water below. I literally stopped playing just to stand and watch that scenery play out for a few minutes. Ahhh!
Then the sun came up and it was time to put some zombies back in the ground.