They do affect the fish. But, if the lights are properly placed and of a good enough quality, then the affect can be positive. To be useful, the lights need to be as close as possible to the natural wavelength of real moonlight. This is about 500nm. This type of light is what fish 'expect' at night and many systems can have their voltage easily changed to simulate moon phases, too. The moonlights are usually operated by a timer, so that they come on a few minutes before the regular lights go off; this way, the fish don't get too startled by a sudden lighting change.
LED lights are generally better at getting close to natural moonlight wavelengths than cold cathode tubes, as these usually peak at about 350nm unless you pay an absoluet fortune for them.
Whichever solution you go for, check the wavelngth of the light that they pump out. There is very little use, from your fishes point of view, to having a night light that is electric disco blue!