I am unable to upload a .csv file in postgresql database because I don't know how to put date structure in sql query

Question

I downloaded a csv file for practising where date format is of two types as shown in picture. the picture is here I tried to change the format to yyyy-mm-dd in excel but it is not happening. and also, I can't upload the file in database in my postgresql. I used the data type "date" but it says I need a different datestyle.

code I have used:

create table sample(
    region varchar,
    country varchar,
    item_type varchar,
    sales_channel varchar,
    order_priority varchar,
    order_date date,
    order_id bigint,
    ship_date date,
    unit_sold int,
    unit_price decimal,
    unit_cost decimal,
    total_revenue decimal,
    total_cost decimal,
    total_profit decimal);

copy sample from 'E:\postgresql\bin\5m Sales Records.csv'
delimiter ',' csv header;

ERROR:  date/time field value out of range: "3/26/2016"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY sample, line 2, column ship_date: "3/26/2016"
SQL state: 22008

any guidance will be helpful, thanks

Answer 1

To summarize comments into sort of an answer:

create table csv_test(id integer, date_fld date);

--CSV file(csv_test.csv
1, 2021-07-11
2, 7/11/2021
3, 7/14/2021

show datestyle ;
 DateStyle 
-----------
 ISO, MDY

 \copy csv_test from '/home/aklaver/csv_test.csv' with csv;
COPY 3


select * from csv_test ;
 id |  date_fld  
----+------------
  1 | 2021-07-11
  2 | 2021-07-11
  3 | 2021-07-14
(3 rows)

set datestyle = 'iso, dmy';
SET

\copy csv_test from '/home/aklaver/csv_test.csv' with csv;
ERROR:  date/time field value out of range: " '7/14/2021'"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY csv_test, line 3, column date_fld: " '7/14/2021'"

CSV values are text, so your date needs only to be in a correctly formatted date style. The second copy failed because the date style date order was 'dmy' and the value is 7/14/2021 and 14 is not a month number. This is why there is a date/month order setting, as 7/11/2021 could either be 'July 11 2021' or 'November 7 2021'. Postgres needs the user to tell it what ordering it is looking at.