Weather is weather, so don't hold me to this....
In my 5 years here, (in a normal world) September is the month to come to Cayman. The ocean is typically glass flat, excellent vis and no currents. There are very slight breezes, but it's not too hot. In addition, the island has far less people here. Some of the restaurants will close up shop for the month, but usually this is just the small ones. It is hands down my favorite time of year here.
October is normally when the weather turns, and we get the steady northeasterly winds. While West Bay is still nice, it's not the amazing tranquilness you get in September.
Now last year, only the first 2 weeks of September had these these conditions and then were hammered with late year tropical storms. Only one came close to us, but we didn't have our normal month of glass, as we call it. In fact we had an article in our blog queue called "September is the time." We never published it due to COVID. According to a long-term hurricane forecast I just read, it predicted an above average hurricane season, but it did show the Western Caribbean and Greater Antilles would be below average.
However...... weather. No guarantees. But if I were a betting man, I'd come in September.
That said, make sure you have your COVID vaccine now, or get it soon. Nothing is cast in stone, but having attended many meetings between CITA and Cayman Government, I can all but assure you that non-vaccinated travelers will not be coming back before 2022.
Tony