Why does Cobalt have different coordination numbers in its complexes with water and thiocyanate?
Firstly, in water Co(II) is not exclusively $\ce{[Co(H2O)6]^2+}$, there is also a small amount of tetrahedral $\ce{[Co(H2O)4]^2+}$, 0.08% at room temperature increasing substantially at high temperature. Canadian Journal of Chemistry, 1980, 58(14): 1418-1426.

According to Greenwood's "Chemistry of the elements" 2nd edition page 1131, the more polarizable the ligand, the more the tetrahedral form of Cobalt (II) is preferred since fewer ligands are required to neutralize the metal ion's positive charge.