Why do bullets shoot through water but not through sand?

I think the main difference is that the molecules of water moves a lot at room temperature, while the grains of sand are static.

That movement is the reason why the shear stresses of liquids are so low, compared with solids. And also why hot metals can be punched deeper than cold ones. The thermal diffusion of the atoms of the cristalline lattice makes the difference.

This also explains why sand poured over one side of an U-tube doesn't climb to the other side. Even if instead of common grains, they were lubricated spheres, they have no reason (no force) to climb.

In the case of a bullet, the random moving water molecules can fill the gap just behind the bullet, due to collisions from molecules close to its head. The grains of sand can not do that. There is no force to move them backward. The action of the bullet is to compact (increase the density) the sand and/or breaking the grains. All that effects take energy away.

They can go through sand--just not very much of it.

There's a case from IIRC Desert Storm. A US tank realized an Iraqi tank was hiding behind a dune from the heat of it's exhaust. It successfully engaged the tank through the sand dune.

The shape
The mechanical displacement of water in water requires less energy than the displacement of sand in sand. This is because the movement of a water molecule through the conglomeration of water molecules requires less energy for the displacement and rotation of each molecule than the displacement of grains of sand.

https://de.wikipedia.org/wiki/Sand

The density and weight
Even if it would be able to mill sand into its atoms, the weight of the sand atoms is twice the weight of water. The displacement and the related to it energy losses will be higher.

Two analogies, to get a closer feeling of the situation for both the bullet in the sand and in water. Let's scale things up and let's not care too much about scale dependency or scale independency, which is another issue.

Imagine we fire a huge bullet into a huge pile of rocks. Say the spatial dimensions of the rocks and bullets are $100$ times as big as the spatial dimensions of the sand grains and the "normal" bullet. What will happen? It's not so difficult to see that the rocks will get damaged when they absorb the kinetic energy of the bullet. So the kinetic energy of the bullet is used to damage the rocks. There is a "damaged rock zone" (sounds like a music program) developing around the top of the bullet. Of course, the rocks in contact with the passing bullet will also heat up due to friction, but this is a minor part of the total absorbed energy. The rocks experience no change in total kinetic energy.

Now imagine firing the same bullet, with the same speed at a volume of tiny unbreakable spheres which have a specific mass that's comparable with that of the rocks. When they move wrt each other they experience very little friction (say they are lubricated). What will happen? The spheres will, just as the rocks, absorb the kinetic energy of the bullet. The bullet pushes the spheres sidewards and the spheres will get kinetic energy which will eventually be gone due to the small friction. Nothing is damaged.

I don't think it's hard to see that the bullet can go a longer distance through the tiny spheres than through (the water) than through the rocks (the sand) before coming to a full stop. You can say this begs the question but these analogies are only meant to get a feeling for the situations. The ultimate reason must be that that damaging and breaking rocks is a more efficient process for absorbing kinetic energy than pushing aside tiny spheres.

Note that the tiny spheres are not in motion before the bullet hits them. I don't think that the motion of the water molecules is of much importance for the absorption of the bullet's kinetic energy (that is if it didn't freeze).

Note also that if the bullet has a flat front side then it's the question if it will penetrate the water (in this case the bullet has to be fired at the water at a right angle). In the analogy, it remains to be seen if a huge bullet with a flat front side will penetrate.

If one refines the analogies a bit, especially the tiny spheres one, (say by letting the tiny spheres have a mutual attraction, providing them with a kind of surface tension), one is able what happens for example at the back of the penetrating bullet.