Silky dreamy water is very popular, especially with reasonably priced neutral density filters and good glass for DLSRs. One of my favorites blog posts shows how this technique can be used to powerful effect. (30-breathtaking-long-exposure-photos-with-water). These shots are simply outstanding and obviously done by someone with a great eye, quality equipment, and enough free time to capture the composition properly.
Can an enthusiast with a midrange DSLR, a mediocre lens, a 2-stop ND filter, and only an afternoon make a similar photo? Well, yes. Is it up to the standard above? Perhaps debatable.
The photo posted below was taken at R.B. Winter State Park in central Pennsylvania. Its a great little park with hiking trails, a sandy beach, places to camp, and a fantastic stream that cuts through it all. There is enough of a slope that there are several small falls and areas of rolling water. A higher resolution version can be found here.
This spot was actually quite cramped. There was a slightly better location in the middle of the stream, but I could not get the tripod in there. The most difficult aspect was the long exposure. A 2 stop ND filter and 200 ISO is really not enough light dampening. Fortunately it was a misty and dreary day, which was all the difference. A 4 stop filter, especially stacked, will have to be in the future.
Post work was relatively minor. Bumped up the vibrance, contrast and saturation. The most significant modification was the top of the image. It was far too bright and drew the eye to the far bank instead of the soft water in the forground. The exposure was dropped about half a stop for opposite side of the stream.