Nevermind, it turned out the documentation is (at least partially) wrong and the "Effective Date" field in the AssemblyItem is determined by the "Effective Revision" field and you must join by it if you want to get it.