Streams and waterfalls are wonderful to shoot especially on cloudy or overcast days. I aim for silky water which requires slow shutter speeds. It you are faced with a sunny day, a neutral density filter (my personal favorite is the Singh Ray Vari ND filter) and/ or polarizer will allow a slower exposure even in bright light.
Examples of Fluid Smooth Slow Motion of Water:
Start with Aperture Priority and set it to a small aperture such as F 22 or higher. ISO 100-200 to start. Look for around 1/2 second Shutter speed. ( Guidelines: around 1/4 if close - up to 1/30 sec if full framed for waterfalls, 1/2 sec if close up to 1/15 full frame for streams and rivers, 1 sec if close up to 1/8 sec full framed for waves or surf).
The camera will select the shutter speed, so the rest is trial and error to get a slow enough shutter speed for the effect you want while not blowing out the highlights too much (that is more than the recovery slider can fix). To assess this, look at the blinkies on your LCD or the histogram.
If you desire the opposite effect, that is to freeze the motion of water, some tips: To freeze the action of water moving parallel to you, start with the goal of 1/500 to 1/1000 sec or higher. 1/250 sec if towards you. 1/250 sec for waterfalls. 1/30 to 1/60 for waves. To freeze raindrops, 1/60 sec.