Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

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

0
Swadhin Ray
Asked:
Swadhin Ray
  • 3
1 Solution
 
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now