What happens?

When running a copy statement, the copy fails if dateformat is explicitly set to AUTO in the copy statement. However, if it is not explicitly set to auto in the copy statement, it's treated as auto and works.

To Reproduce

To reproduce, use these CSVs:
date_example_1.csv
date_example_2.csv

And run the following code:

create or replace temp table date_tests (col1 date);

-- Working cases (AUTO is Auto-Detected):
copy date_tests from 'date_example_1.csv';
copy date_tests from 'date_example_2.csv';

-- Failed cases (AUTO is Set By User)
-- file's date format is YYYY-MM-DD
copy date_tests from 'date_example_1.csv' WITH (dateformat 'AUTO');
-- file's date format is %m/%d/%y
copy date_tests from 'date_example_2.csv' WITH (dateformat 'AUTO');

Error messages (truncated):
Failed case 1

SQL Error: Conversion Error: CSV Error on Line: 2
Original Line: 2023-12-17
Error when converting column "col1". Could not convert string "2023-12-17" to 'DATE'
...
CSV Sniffer: Sniffer detected value different than the user input for the Date Format options 
 Set: AUTO Sniffed: 

Failed case 2

SQL Error: Conversion Error: CSV Error on Line: 2
Original Line: 12/17/23
Error when converting column "col1". Could not convert string "12/17/23" to 'DATE'
...
CSV Sniffer: Sniffer detected value different than the user input for the Date Format options 
 Set: AUTO Sniffed: %m/%d/%y

You can also recreate the csv's yourself with the below data.

date_example_1.csv:
date_example
2023-12-17
2023-12-14
2023-12-20

date_example_2.csv:
date_example
12/17/23
12/14/23
12/20/23

OS:

macOS 15.1.1 (24B91)

DuckDB Version:

1.1.3

DuckDB Client:

dbeaver

Hardware:

No response

Full Name:

Ryan Waldorf

Affiliation:

Universql

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have