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

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(at)rocketmail(dot)com>:

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>