Dispel magic on a target creature ends spells that affect that creature. Does casting dispel magic on another caster with an active spiritual weapon dispel the spiritual weapon, or does dispel magic need to be cast directly at the weapon?
You must target the spiritual weapon.
Part of the description of Dispel Magic:
Choose one creature, object, or magical effect within range. Any spell of 3rd level or lower on the target ends.
Part of the description of Spiritual Weapon:
The weapon can take whatever form you choose. Clerics of deities who are associated with a particular weapon (as St. Cuthbert is known for his mace and Thor for his hammer) make this spell's effect resemble that weapon.
So first, the spectral weapon is described as being the Spiritual Weapon spell's effect, meaning it is the valid target for Dispel Magic, which means targeting the caster would not dispell the effect !
Also, because Spiritual Weapon is a level 2 spell, its effect - the weapon - is then dispelled without any ability check.
My reading is that the intent of dispel magic is to remove magical stuff cast on a person, not necessarily magical stuff cast by a person.
In the case of spiritual weapon, the effect is not on the caster, it is in the air tens of feet from the caster.
I would apply this to other spells, for example arcane eye, the various image and illusion spells, buffing/cursing spells like bless and bane, glyph of warding, sanctuary.
I would also say that detect magic cast near someone using spiritual weapon would show the magic aura on the weapon, not the caster.
If you did rule that this worked when targeted on the caster then you'd have to decide whether or not casting dispel magic on the effect also works. For example, do you allow casting dispel magic on a blessed/baned person or on the caster or on both?
I believe the intent of the OP's question was to ask "if I cast Dispel Magic on the caster who created a Spiritual Weapon, will said weapon be dispelled?"
The answer to that - assuming I'm correct - would be "no." You can dispel the spiritual weapon by casting Dispel Magic on the weapon itself, but not by casting Dispel Magic on the spellcaster who created it. The spell doesn't affect the one who casts it (unless he casts it on himself, of course, which doesn't apply to SW.)
Duration: 1 minute
You create a floating, spectral weapon within range that lasts for the duration or until you cast this spell again. [...]
Choose one creature, object, or magical effect within range. Any spell of 3rd level or lower on the target ends. [...]
RAW, Dispel Magic removes a spell applied on the target. Spiritual Weapon is not a spell applied on a target, it is just a floating magical effect. As for your second question:
or does dispel magic need to be cast directly at the weapon?
actually, I am not sure this is going to work either. Spiritual Weapon is a valid target (magical effect), but there are no spells applied to that target. However, as a DM I would allow it.