Destroyers used to carry many torpedo tubes intended for launching torpedo attacks against surface ships, however nowadays all the torpedoes carried by destroyers are for ASW purposes. Why and when did this change? Are torpedoes launched by surface ships against other surface ships not effective anymore?
Surface launched torpedoes against surface ships aren't practical anymore because missiles out range torpedoes significantly. A destroyer is better off carrying anti-ship missiles than shorter range torpedoes.
You can see this in maritime patrol aircraft like the P3 and P8 which carry Harpoon anti-ship missiles as well as ASW torpedoes.
As for when and why - when torpedoes acquired homing capabilities, they made them practical weapons against the deeper diving and faster moving submarines of the Cold War.
Prior to that, ASW often focused on dropping depth charges on top of or near the destroyer. As subs could now dive deep and move faster than ever before, that was no longer as practical and thus the homing torpedo became the weapon of choice.