Swadhin Ray
asked on
SQL in Oracle
Hello Experts,
I have two tables which should contain dates but the issue is like one table is having varchar2 column and another is as date.
Now for example I have one table like source :
Where I have data like :
SQL> select * from sloba_date_1 ;
Now I want o insert into the other target table i.e. like :
Issue is like I have the data like "23 2 14" which need to be converted as "23-FEB-14" on both columns.
So finally the data should be inserted into the target table i.e. sloba_date_stg
the data should look like :
I have two tables which should contain dates but the issue is like one table is having varchar2 column and another is as date.
Now for example I have one table like source :
create table sloba_date_1
(
END_DATE VARCHAR2(4000),
START_DATE VARCHAR2(4000)
);
Where I have data like :
BEGIN
insert into sloba_date_1 values('23-JAN-14','23-JUN-14');
INSERT INTO sloba_date_1 VALUES('2-JAN-14','23-JUN-14');
insert into sloba_date_1 values('23 01 14','23 2 14');
INSERT INTO sloba_date_1 VALUES('23-JAN-14','23-JUN-14');
COMMIT;
END;
SQL> select * from sloba_date_1 ;
END_DATE START_DATE
-------------------------------------------------------------------------------- --------------
23-JAN-14 23-JUN-14
2-JAN-14 23-JUN-14
23 01 14 23 2 14
23-JAN-14 23-JUN-14
Now I want o insert into the other target table i.e. like :
CREATE TABLE sloba_date_stg
(
END_DATE DATE,
START_DATE DATE
)
;
Issue is like I have the data like "23 2 14" which need to be converted as "23-FEB-14" on both columns.
So finally the data should be inserted into the target table i.e. sloba_date_stg
the data should look like :
END_DATE START_DATE
-------------------------------------------------------------------------------- --------------
23-JAN-14 23-JUN-14
2-JAN-14 23-JUN-14
23-JAN-14 23-FEB-14
23-JAN-14 23-JUN-14
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok got it:
select to_date( start_date
, case when start_date like '%-%-%' then 'dd-MON-yy'
else 'dd mm yy'
end) new_start_date
, to_date( end_date
, case when end_date like '%-%-%' then 'dd-MON-yy'
else 'dd mm yy'
end) new_end_date
from sloba_date_1 ;
ASKER
Thanks a ton.
Regards,
Sloba
Regards,
Sloba
ASKER
Open in new window