I have a column as date (CHARACTER VARYING) in table And it may contain dd/mm/yyyy or mm/dd/yyyy format dates. Now i need to convert all dates in dd/mm/yyyy ..

 select   c.trial_id,c.name,c.gender,age(cast(c.dob as date)) as p_age,c.relationship_name,c.relationship,c.dob,c.staff_id from mas_patient_details c

Even i tried doing like this

 select   to_char(dob::date, 'DD/MM/YYYY')from mas_patient_details

both of query returns

ERROR: date/time field value out of range: "12/21/1989" HINT: Perhaps you need a different "datestyle" setting. ********** Error **********

ERROR: date/time field value out of range: "12/21/1989" SQL state: 22008 Hint: Perhaps you need a different "datestyle" setting.