I am trying to import the date columns with different types of date format.
table name: fD
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 complicate 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;
'''
0 comments:
Post a Comment
Thanks