Perhaps you need a different "datestyle" setting

Question

I have a table which have a column order_date of date type.

query:

INSERT INTO uni_data_temp(sale_order_item_code, 
            order_date, sale_order_item_status, tracking_number, dispatch_date, 
            user_id) VALUES ('1000932515',  cast('16/05/2015' as date), 'DISPATCHED', 'UNIPAYP1958141', '2015/05/20', '4')

when I am running this query it gives error:

ERROR: date/time field value out of range: "16/05/2015"
SQL state: 22008
Hint: Perhaps you need a different "datestyle" setting.
Character: 380

then I changed the query

INSERT INTO uni_data_temp(sale_order_item_code, 
            order_date, sale_order_item_status, tracking_number, dispatch_date, 
            user_id) VALUES ('1000932515',  cast('2015/05/16' as date), 'DISPATCHED', 'UNIPAYP1958141', '2015/05/20', '4')

It works fine.

but my problem is my date may be in any style (yyyy/mm/dd or dd/mm/yyyy) how i can cast it according databse ?

Any kind of date format convert into system database.

Thank You

Solution 1

You are using strings for the dates and are relying on session settings to interprete the strings correctly. Use supported date literals instead so as to be independent from settings.

In PostgreSQL (and the SQL standard for that matter) DATE 'YYYY-MM-DD' is considered a date literal and is the format I would recommend. So use

INSERT INTO uni_data_temp
  ( sale_order_item_code
  , order_date
  , sale_order_item_status
  , tracking_number
  , dispatch_date
  , user_id
  ) 
VALUES 
  ( '1000932515'
  , DATE '2015-05-16'
  , 'DISPATCHED'
  , 'UNIPAYP1958141'
  , DATE '2015-05-20'
  , 4
  );

(Thanks to a_horse_with_no_name for pointing me to the correct date literal syntax in PostgreSQL.)

If, however, you get the dates as strings from somewhere, you need to apply the according format:

TO_DATE('06/05/2015', 'DD/MM/YYYY')
TO_DATE('05/06/2015', 'MM/DD/YYYY')

Solution 2

In your postgresql.conf you have a setting called datestyle it is composed of two parts out and in. Might be set as iso, mdy (output as ISO and input as american) try setting it as iso, dmy