Re: Import CSV date issues

My experience is that the best date format is year-month-day YYYY-MM-DD

Otherwise you have to set the "set date " in sql

________________________________
From: Sarfraz Nawaz <sarfraz(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Sent: Sunday, March 3, 2013 7:13 AM
Subject: [NOVICE] Import CSV date issues

Hello everyone,

I am just setting up a db using PostgreSQL-9.1 and having issues with
importing from a CSV file. I have created a table "durations" in
"mydb" using the following SQL command,

CREATE TABLE durations ( startdate date, enddate date, starttime time
without time zone, endtime time without time zone, days integer, hours
integer, minutes integer, somefield text, locid integer  );

mydb has the proper date style set as well as shown with this,

mydb=> SHOW datestyle;
DateStyle
-----------
ISO, DMY
(1 row)

However, when I try to import data into "durations" table from a CSV
file, I get this error which is puzzling me because the date in the
CSV file is in the same DMY format that the db should be expecting. I
have also tried by giving "durations(startdate, enddate, ....)" format
as well but same result.

mydb=> \copy durations FROM 'myfile.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type date: "12/09/2011"
CONTEXT:  COPY durations, line 1, column startdate: "12/09/2011"

The lines in the CSV file look like the following,

12/09/2011,12/09/2011,11:12,12:12,0,1,60,NO,82889

Any suggestions please? Thank you for reading through :)