REGR: fix roundtripping datetimes with sqlite type detection
- [x] closes #55554
- [x] [Tests added and passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#writing-tests) if fixing a bug or adding a new feature
- [ ] All [code checks passed](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#pre-commit).
- [ ] Added [type annotations](https://pandas.pydata.org/pandas-docs/dev/development/contributing_codebase.html#type-hints) to new arguments/methods/functions.
- [x] Added an entry in the latest `doc/source/whatsnew/vX.X.X.rst` file if fixing a bug or adding a new feature.

https://github.com/pandas-dev/pandas/pull/54985 was already a related regression fix in 2.1.1, but that only fixed the adapter (conversion to sqlite), not the converter (conversion back from sqlite to python). This converter only gets used when explicitly asking sqlite to detect dtypes.
The changes LGTM assuming this is green. Owee, I'm MrMeeseeks, Look at me.

There seem to be a conflict, please backport manually. Here are approximate instructions:

1. Checkout backport branch and update it.

```
git checkout 2.1.x
git pull
```

2. Cherry pick the first parent branch of the this PR on top of the older branch:
```
git cherry-pick -x -m1 aeb3644567a10296cc09aed03bf7e0a3780e67ee
```

3. You will likely have some merge/cherry-pick conflict here, fix them and commit:

```
git commit -am 'Backport PR #55690: REGR: fix roundtripping datetimes with sqlite type detection'
```

4. Push to a named branch:

```
git push YOURFORK 2.1.x:auto-backport-of-pr-55690-on-2.1.x
```

5. Create a PR against branch 2.1.x, I would have named this PR:

> "Backport PR #55690 on branch 2.1.x (REGR: fix roundtripping datetimes with sqlite type detection)"

And apply the correct labels and milestones.

Congratulations — you did some good work! Hopefully your backport PR will be tested by the continuous integration and merged soon!

Remember to remove the `Still Needs Manual Backport` label once the PR gets merged.

If these instructions are inaccurate, feel free to [suggest an improvement](https://github.com/MeeseeksBox/MeeseeksDev).
                 Manual backport -> https://github.com/pandas-dev/pandas/pull/55702