?
Solved

SQL in Oracle

Posted on 2014-02-05
4
Medium Priority
?
466 Views
Last Modified: 2014-02-05
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

0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 39835192
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39835229
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
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39835236
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
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39835237
Thanks a ton.

Regards,
Sloba
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question