select col1, col_name from temp where col1 = 1234
col1  col_name
1234  0/0/0000

select col1, to_char(col_name,'MM/DD/YYYY') from temp where col1 = 1234
col1   col_name
1234   12/02/0000

SQL> desc temp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 Col1                                                NUMBER(15)
 Column_name                                         DATE

SQL> select to_date('12/02/0000', 'dd/mm/yyyy') from dual;
select to_date('12/02/0000', 'dd/mm/yyyy') from dual
               *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0


SQL>

SQL> select to_char(start_date, 'MM/DD/YYYY') from loaner_act_1 where act_id = 958;

TO_CHAR(START_DATE,'MM/DD/Y
---------------------------------------------------------------------------
12/02/1900
12/02/0000

SQL> select to_date(to_char(start_date, 'MM/DD/YYYY'),'MM/DD/YYYY') from loaner_act_1 where ac
t_id = 958;
ERROR:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0



no rows selected

SQL> select dump(start_date) from loaner_act_1 where act_id = 958;

DUMP(START_DATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 119,100,12,2,1,1,1
Typ=12 Len=7: 100,100,12,2,1,1,1

select ADD_MONTHS(start_date,22800)  
from loaner_act_1 
where act_id = 958 and to_char(start_date,'YYYY') = '0000';

ADD_MONTHS
----------
12/02/1900

select to_date(to_char(ADD_MONTHS(start_date,22800),'MM/DD/YYYY'),'MM/DD/YYYY')  
from loaner_act_1 
where act_id = 958 and to_char(start_date,'YYYY') = '0000';

TO_DATE(TO
----------
12/02/1900

SQL> select to_char(start_DATE,'MM/DD/YYYY') 
from loaner_act_1 
where to_char(START_DATE,'DD') = '00'

TO_CHAR(START_DATE,
---------------------------------------------------------------------------
01/00/5267
01/00/5267

create or replace view loaner_act_v  as
select
 case
 when to_char(START_DATE,'DD') = '00' then 
    to_date(to_char(START,'MM/')||to_char(01)||to_char(START_DATE,'/YYYY'),'MM/DD/YYYY')
 end as START_DATE
  from LOANER_ACT_1 a
  where to_char(START_DATE,'DD') = '00';

SQL> select *  from LOANER_ACT_V;
select *  from LOANER_ACT_V
                          *
ERROR at line 1:
ORA-01843: not a valid month

SQL> select  case
  2   when to_char(START_DATE,'DD') = '00' then 
to_date(to_char(START_DATE,'MM/')||to_char(01)||to_char(START_DATE,'/YYYY'),'MM/DD/YYYY')
  3   end as START_DATE
  4    from LOANER_ACT_1 a
  5    where to_char(START_DATE,'DD') = '00';

START_DATE
----------
01/01/5267
01/01/5267

NLS_DATE_FORMAT                MM/DD/YYYY

 y_char := to_char(i_date, 'YYYY');
  m_char := to_char(i_date, 'MM');
  d_char := to_char(i_date, 'DD');
  if i_date is null then
    s_date := null;
  elsif y_char = '0000' then
    s_date := add_months(i_date, 22800);
  elsif m_char = '00' then
    s_date := to_date(replace(rpad(extract(month from i_date), 2, 1) || '/' ||
                              lpad(extract(day from i_date), 2, 0) || '/' ||
                              extract(year from i_date),
                              '-',
                              ''),
                      'MM/DD/YYYY');
  elsif d_char = '00' then
    s_date := to_date(replace(lpad(extract(month from i_date), 2, 0) || '/' ||
                              rpad(extract(day from i_date), 2, 1) || '/' ||
                              extract(year from i_date),
                              '-',
                              ''),
                      'MM/DD/YYYY');
  else
    s_date := i_date;
  end if;