Yes, Cruise Missiles can destroy other units. However, since their rate of fire is only 2 or 3 (I can't remember), regular/veteran/elite units have to be weakened before a cruise missile can finish them off, since the Cruise Missile can only take off 2-3 Hps max.
For example, during my last One City Challenge game I had huge amounts of Artillery and Cruise Missiles. If an enemy ship came near my coast, I would artillery bombard them down to 1 Hit Point, then launch a Cruise Missile at them to finish them off.
Very satisfying to take out a battleship with some arty fire and a single 6 shield Cruise Missile.
BTW, I found Cruise Missiles to be VERY useful during that One City game, since the only resource I had was aluminum - no oil or uranium. However, with my stack of 15 Artillery and the ability to pump out a Cruise Missile (requires only Aluminum to build) every turn, my sole city was never in serious danger of being captured. I won that game by Diplomatic Victory, but while I was building the UN the Aztecs massed around 10 Modern Armor next to my city (we had a MPP, I though it would dissuade them from attacking but nope . . .). They declared war once I asked them to leave, but my aforementioned artillery and 16 stacked Cruise Missiles were more than enough to handle them.
Yes, once the patch hits Cruise Missiles will lose some of their flavor, but I've found them very useful in certain situations.