Re: HINT: Perhaps you need a different "datestyle" setting - postgresql

[Pavel Stehule <pavel.stehule@xxxxxxxxx>]

Hi

try

postgres=# set datestyle to DMY;
SET
postgres=# SELECT '19/08/2014'::date;
    date   
------------
 2014-08-19
(1 row)

Postgres supports following styles only:

DEFAULT      EUROPEAN     ISO          NONEUROPEAN  SQL          YMD         
DMY          GERMAN       MDY          POSTGRES     US          

or more exactly:

postgres=# set datestyle to SQL,DMY;
SET
postgres=# SELECT '19/08/2014'::date;
    date   
------------
 19/08/2014
(1 row)

http://www.postgresql.org/docs/9.4/static/datatype-datetime.html

Regards

Pavel Stehule

2014-12-28 11:43 GMT+01:00 Arup Rakshit <aruprakshit@xxxxxxxxxxxxxx>:

Hi,

I am actually trying to copy data from my csv file to DB. But I am getting the
DateStyle error.

I tried different ways to make it workable. But no luck!

prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV
DELIMITER ',' HEADER ;
ERROR:  date/time field value out of range: " 19/08//2014"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY orders, line 2, column delivery_date: " 19/08//2014"
prac_db=# SET datestyle = "ISO";
SET
prac_db=# copy orders from '/home/arup/postgresql/order.csv' with CSV
DELIMITER ',' HEADER ;
ERROR:  date/time field value out of range: " 19/08//2014"
HINT:  Perhaps you need a different "datestyle" setting.
CONTEXT:  COPY orders, line 2, column delivery_date: " 19/08//2014"
prac_db=# SET datestyle = "ISO, IS";
ERROR:  invalid value for parameter "DateStyle": "ISO, IS"
DETAIL:  Unrecognized key word: "is".
prac_db=# SET datestyle = "ISO, IS 7900:2001";
ERROR:  invalid value for parameter "DateStyle": "ISO, IS 7900:2001"
DETAIL:  List syntax is invalid.
prac_db=# SET datestyle = "ISO, BIS IS";
ERROR:  invalid value for parameter "DateStyle": "ISO, BIS IS"
DETAIL:  List syntax is invalid.
prac_db=# SET datestyle = "ISO, BIS IS 7900:2001";
ERROR:  invalid value for parameter "DateStyle": "ISO, BIS IS 7900:2001"
DETAIL:  List syntax is invalid.
prac_db=#

My Data style inside the CSV exactly like -
http://en.wikipedia.org/wiki/Date_and_time_notation_in_India. How to fix this
problem?

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general