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 :

create table  sloba_date_1
(
END_DATE                   VARCHAR2(4000),
START_DATE                 VARCHAR2(4000)
);

Open in new window


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;

Open in new window


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

Open in new window


Now I want o insert into the other target table i.e. like :

CREATE TABLE sloba_date_stg
(
END_DATE                   DATE,
START_DATE                 DATE         
)
;

Open in new window


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

Open in new window

LVL 17
Swadhin RaySenior Technical Engineer Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
something like this might do:
select to_date( start_date
, case when start_date like '%-%-%' then 'dd-MON-yy' 
else 'dd m yy' 
end) new_start_date
, to_date( end_date
, case when end_date like '%-%-%' then 'dd-MON-yy' 
else 'dd m yy' 
end) new_end_date
 from sloba_date_1 ;

Open in new window

this will "fail" if any of the values does not match the pattern above, you can add more "when  ... then" lines above to catch more pattern
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Swadhin RaySenior Technical Engineer Author Commented:
If i used the same set of the data that I have provided , and using the SQL provided by you getting me the below error:

SELECT to_date(start_date
              ,CASE
                       WHEN start_date LIKE '%-%-%' THEN
                        'DD-MON-YY'
                       ELSE
                        'dd m yy'
               END) new_start_date
      ,to_date(end_date
              ,CASE
                       WHEN end_date LIKE '%-%-%' THEN
                        'DD-MON-YY'
                       ELSE
                        'dd m yy'
               END) new_end_date
  FROM sloba_date_1
 
ORA-01821: date format not recognized
 

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
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 ;

Open in new window

0
 
Swadhin RaySenior Technical Engineer Author Commented:
Thanks a ton.

Regards,
Sloba
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.