BUG: astype not working correctly for similar datetime format

[dipen-shrestha-ttm]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
a = pd.Series(["2007-07-07 01:01:01.10", "2007-07-07 01:01:01"])
a.astype("datetime64[ns]")

Issue Description

This code runs fine in pandas 1.5.3 If same column has multiple format for date time, when we use as type it fails.

ValueError: time data "2007-07-07 01:01:01" doesn't match format "%Y-%m-%d %H:%M:%S.%f", at position 1. You might want to try:
- passing format if your strings have a consistent format;
- passing format='ISO8601' if your strings are all ISO8601 but not necessarily in exactly the same format;
- passing format='mixed', and the format will be inferred for each element individually. You might want to use dayfirst alongside this.

Expected Behavior

0 2007-07-07 01:01:01.100
1 2007-07-07 01:01:01.000
dtype: datetime64[ns]

Installed Versions

pandas version: 2.0.1

[jbrockmendel]

cc @MarcoGorelli i think this is related to PDEP4

[MarcoGorelli]

thanks for the report - yes, this was intentionally changed in pdep4

you can do

In [4]: to_datetime(["2007-07-07 01:01:01.10", "2007-07-07 01:01:01"], format='ISO8601').to_series()
Out[4]:
2007-07-07 01:01:01.100   2007-07-07 01:01:01.100
2007-07-07 01:01:01.000   2007-07-07 01:01:01.000
dtype: datetime64[ns]

I'm tempted to disallow converting from string to datetime with astype('datetime64[ns]'), shouldn't users use to_datetime?