将Pandas DataFrame写入Snowflake时日期列报错求助

Hey there! Let’s work through this frustrating date formatting issue you’re hitting. The errors you’re seeing boil down to two key problems: Snowflake not recognizing your original date format, and hidden bad data in your column causing issues when converting to datetime. Here’s how to fix it:

Step 1: Fix the Date Parsing (and Catch Bad Data) #

Your first error happens because Snowflake expects dates in a standard format like YYYY-MM-DD by default, but your data is in DD-MM-YYYY (e.g., 29-11-2019). When you tried using astype('datetime64[ns]'), you hit a new error because your REPORTDATE column has non-date values (like 00:24.3) that Pandas is trying to force into a datetime type incorrectly.

Instead of astype, use pd.to_datetime with explicit formatting and error handling to clean this up:

# Parse dates with DD-MM-YYYY format, turn invalid values into NaT (Not a Time)
df['REPORTDATE'] = pd.to_datetime(df['REPORTDATE'], format='%d-%m-%Y', errors='coerce')

# Now check for rows that failed parsing (NaT values)
bad_rows = df[df['REPORTDATE'].isna()]
print(bad_rows)

This will show you exactly which rows have wonky values like 00:24.3. You’ll need to decide how to handle these:

• If they’re typos, correct the original data.
• If they’re supposed to be time values (not dates), you might need to split them into a separate column or adjust your logic.
• If they’re irrelevant, drop those rows with df = df.dropna(subset=['REPORTDATE']).

Step 2: Ensure Proper Data Type Matching with Snowflake #

Once your REPORTDATE column is clean and standardized to datetime64, make sure the data type aligns with your Snowflake table:

Option 1: Write as Date Type #

If your Snowflake column is defined as DATE, specify the dtype explicitly when writing to avoid conversion issues:

from sqlalchemy import types

# Define dtype mapping for your column
dtype_mapping = {
    'REPORTDATE': types.Date()
}

# Write to Snowflake
df.to_sql(
    name='your_snowflake_table',
    con=your_snowflake_sqlalchemy_engine,
    if_exists='append',  # or 'replace' depending on your needs
    dtype=dtype_mapping
)

Option 2: Convert to Snowflake-Friendly String Format #

If you prefer, you can convert the cleaned datetime column to a string in YYYY-MM-DD format (Snowflake’s default date format) before writing:

# Convert datetime to YYYY-MM-DD string
df['REPORTDATE'] = df['REPORTDATE'].dt.strftime('%Y-%m-%d')

# Now write to Snowflake (ensure the target column is DATE type)
df.to_sql('your_snowflake_table', con=your_snowflake_engine, if_exists='append')

Step 3: Troubleshoot the Timestamp '00:24.3' Error #

That weird Timestamp '00:24.3' error comes from Pandas trying to parse a time-only string as a full timestamp (defaulting to the Unix epoch: 1970-01-01 00:24:00.300). Snowflake might reject this if your target column is a DATE type (not TIMESTAMP), or if the value doesn’t make sense for your use case. The pd.to_datetime with errors='coerce' step above will catch these so you can fix the root cause in your data.

内容的提问来源于stack exchange，提问作者WarBoy