CODEWITHSUNDEEP
oracle-databasedate
SSW
SSW

Reputation: 41

oracle developer_ importing different forms of date in one column

I am trying to import the date columns with different types of date format.

table name: fD (1604294 size of csv file)
column name: TRANS_DATE_TRANS_TIME

this column has two forms of the date like below.
date type1: 'MM/DD/YYYY HH24:MI'
date type2: 'YYYY-MM-DD HH24:MI:SS'

not surprisingly, SQL developer(oracle) cannot import this column as a DATE type. I aim to unify these forms as 'MM/DD/YYYY HH24:MI.'

What I did was I import it as a VARCHAR2 type and try to write very complicate and difficult(?) query... and then I read someone wrote here that do not import date type data as a VARCHAR2 because of this. Then, is there any idea that I can import this column successfully? or I need to import it as a VARCHAR2 type and write down complicated query? (and I do not know this query either.. haha)

this is what I did when I imported it as a VARCHAR2.

with DT as(
select TRANS_DATE_TRANS_TIME from fD
)
select TRANS_DATE_TRANS_TIME,
replace(to_char(to_date(TRANS_DATE_TRANS_TIME, case 
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{1}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{1}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{2}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{1}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{1}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{1}/[0-9]{2}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{1}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{2}/[0-9]{2}/[0-9]{4} [0-9]{2}:[0-9]{2}') then 'MM/DD/YYYY HH24:MI'
when regexp_like(TRANS_DATE_TRANS_TIME,'[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}') then 'MM/DD/yyyy HH24:MI'
end), 'MM/DD/yyyy HH24:MI'),'') as newT from DT;

Upvotes: 0

Views: 33

Answers (3)

SSW
SSW

Reputation: 41

I just solved this problem so I am sharing what I did here! I imported the column as VARCHAR2 and then used this.

-- query test before updating table
select (case when instr(TRANS_DATE_TRANS_TIME , '-') > 1 then 
             to_char(to_date(TRANS_DATE_TRANS_TIME , 'YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY HH24:MI') 
        else to_char(to_date(TRANS_DATE_TRANS_TIME , 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI') 
        end) field_as_date 
 from fd;



-- update the table as 'MM/DD/YYYY HH24:MI' based on the test

UPDATE fd
SET TRANS_DATE_TRANS_TIME = case when instr(TRANS_DATE_TRANS_TIME , '-') > 1 then 
             to_char(to_date(TRANS_DATE_TRANS_TIME , 'YYYY-MM-DD HH24:MI:SS'),'MM/DD/YYYY HH24:MI') 
        else to_char(to_date(TRANS_DATE_TRANS_TIME , 'MM/DD/YYYY HH24:MI'), 'MM/DD/YYYY HH24:MI') 
        end;



-- check if 'YYYY-MM-DD HH24:MI:SS' row is transformed into 'MM/DD/YYYY HH24:MI'

select * from (select rownum as num, trans_date_trans_time from fd) a where a.num=1048680;
-- transformed!


-- check if there is null compared to the size of 1604294
select count(trans_date_trans_time)  from fd; 
--  => 1604294 (no null)

Thank you people who answered this post for me!

Upvotes: 0

MT0
MT0

Reputation: 168470

From Oracle 12, just try to convert the value to a DATE using TO_DATE and include the DEFAULT NULL ON CONVERSION ERROR option and then use COALESCE to try the different formats:

SELECT TRANS_DATE_TRANS_TIME,
       COALESCE(
         TO_DATE(
           TRANS_DATE_TRANS_TIME DEFAULT NULL ON CONVERSION ERROR,
           'MM/DD/YYYY HH24:MI'
         ),
         TO_DATE(
           TRANS_DATE_TRANS_TIME DEFAULT NULL ON CONVERSION ERROR,
           'YYYY-MM-DD HH24:MI:SS'
         )
       ) as newT
FROM   DT;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 143053

How about simple two-step approach?

insert into target_table (column list goes here)
select ...,
       to_date(date_value_column, 'mm/dd/yyyy hh24:mi')
from source_table;

and then

insert into target_table (column list goes here)
select ...,
       to_date(date_value_column, 'yyyy-mm-dd hh24:mi:ss')
from source_table;

Because, all rows - that don't fit specified date format - will be discarded in the 1st query and loaded with the 2nd one (and vice versa).

Upvotes: 0

Related Questions