For the most part, when looking at a monitor, chromatic adaptation is pretty good. In other words, RGB #FFFFFF looks white, not light blue, and #808080 looks gray, not bluish-gray. It does start falling apart when you mix different lighting, for example holding a white piece of paper to the screen under normal room light.
I think the main reason D65 is chosen for the sRGB white point is that it is fairly accurate, in other words it's pretty close to the actual white point of the monitor I'm looking at now (Dell P2415Q).
I have yet to find a source on chromatic adaptation that I consider a really excellent tutorial. Certainly the Wikipedia page is cursory and abstract, which is a shame.