sql - change date format to yyyy-mm-dd from varchar in postgres

Question

SELECT
  to_char("date", 'YYYY/MM/DD') 
  "public".teacher_details.teacher_id,
  "public".teacher_details.first_name,
  "public"."TblFacultyMaster"."MastCode",
  "public"."TblFacultyMaster"."MastName",
  "public"."TblFacultyMaster"."DOB",
  "public".teacher_details.dob
FROM
  "public".teacher_details
INNER JOIN "public"."TblFacultyMaster" ON "public"."TblFacultyMaster".teacher_id = "public".teacher_details.teacher_id

I am trying to convert date format from dd/mm/yyyy to yyyy-mm-dd and the type is varchar , since I have yyyy-mm-dd in format in my TblFacultyMaster table and dd/mm/yyyy in my teachers_deatil table I want to match common DOB but the format is different in both table

Help Please

Thanks In advance

Answer 1

to_char can not convert string to string, try to typecast with date to the varchar datecolumn here i suppose to "date" is your that column, try like below :

SELECT
  to_char("date"::date, 'YYYY/MM/DD') as date, 
  "public".teacher_details.teacher_id,
  "public".teacher_details.first_name,
  "public"."TblFacultyMaster"."MastCode",
  "public"."TblFacultyMaster"."MastName",
  "public"."TblFacultyMaster"."DOB",
  "public".teacher_details.dob
FROM
  "public".teacher_details
INNER JOIN "public"."TblFacultyMaster" ON "public"."TblFacultyMaster".teacher_id = "public".teacher_details.teacher_id