Perhaps you need a different "datestyle" setting

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

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')

Use something like below query providing correct formats:

UPDATE table name SET column2  = to_date(column1, 'dd-MM-yyyy HH24:MI:SS')