Fix StringArray.astype for category dtype
- [X] closes #40351 
- [X] tests added / passed
- [X] Ensure all linting tests pass, see [here](https://pandas.pydata.org/pandas-docs/dev/development/contributing.html#code-standards) for how to run them
- [ ] whatsnew entry

This was failing due to `elif np.issubdtype(dtype, np.floating)` [here](https://github.com/pandas-dev/pandas/blob/master/pandas/core/arrays/string_.py#L330), which fails for the pandas dtypes.
StringArrays are now being cast to Categorical just as they were before https://github.com/pandas-dev/pandas/pull/38530, when the casting still happened inside `Block`.
This uncovered a bit of a larger issue, basically #37626, but for datetimes & period.

Example: 
```python
import pandas as pd
t = pd.Series(["1/1/2021", "2/1/2021", None], dtype="period[M]")
s = pd.Series(["1/1/2021", "2/1/2021", None], dtype="string").astype("period[M]")
```

`t` work, `s` raises because the `None` is converted to `NA` which isn't recognized as datetimelike by period.

Example
```python
import pandas as pd
t = pd.Series(["1/1/2021", "2/1/2021"], dtype="object").astype("datetime64[ns]")
s = pd.Series(["1/1/2021", "2/1/2021"], dtype="string").astype("datetime64[ns]")
```
`t` work, `s` raises.

However if it's ok I'd open a new issue for it, since it'll be a larger revamp which will take me a few more days.
These "new" issues also aren't regressions like this Categorical issue, they're all already present in 1.2.3. Yes, that's a more general issue, and can be left out of scope for this PR. > needs a whatsnew note for 1.2.4

This doesn't need a whatsnew, it's only a regression on master > > needs a whatsnew note for 1.2.4
> 
> 
> 
> This doesn't need a whatsnew, it's only a regression on master

ok great Why does Codecov say 0% coverage for the diff, when the added `if`-branch has multiple tests running over it? @jreback added a Whatsnew, should be good for merge. thanks @siboehm 