The aim is for them to function as real stepped or spillway weirs, over which water must flow in order to pass. If the water level is lower than the crest, water is completely blocked except for some minor leakage through the soil, as with all ground tiles in the game.

The regular Weir keeps the area quite dry, but not too dry, aiming for a water level one metre below the surface. This usually allows enough water to flow away, while also letting it sink into the ground to benefit the trees in case of a drought. But if you want to bigger forests or fields you need a weir with a higher crest.
The High Weir is good if you want to keep the water at a high level for fields or bigger forests, but also helps if there is a lot of rain. It aims at a water level of 0.4m below surface.
The Spillway Weir is good for dry areas of if you want to fully block the river up to the surface level or let the water flow over the land, which may be beneficial for some cases to fill the ground water reserves.

I hope this helps. Best is to experiment a bit around and observe the area in dry or rainy periods.