Maybe you need another "datestyle" setting

Question

I have a table with order_date column of date type.

request:

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 run this query, it gives an 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 request

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 great.

but my problem is that my date can be in any style (yyyy / mm / dd or dd / mm / yyyy), how can I use it according to the database data?

Convert any date format to a system database.

thanks

Answer 1

, . , .

PostgreSQL ( SQL-) DATE 'YYYY-MM-DD' , .

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

( a_horse_with_no_name PostgreSQL.)

, , -, :

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