sql - Combining multiple VARCHAR fields as 1 DATE when inserting from one table to another in Oracle? -
i'm attempting create short script take of fields table1 , insert them table2. basic fields (col1, col2, col3, etc.) have got covered this:
insert table1 (col1, col2, col3, col4, col5, col6, col7) select col1, col2, col3, col4, col5, sysdate, user table2
for col8
, col9
in table1 i'm little stuck though. col8/col9
instance date
datatypes need inserted combining col6/col7/col8
, col9/col10/col11
table1 respectively together. each of these defined follows:
col6/col9 -- varchar2 (2byte) // month col7/col10 -- varchar2 (2byte) // day col8/col11 -- varchar2 (4byte) // year
looking online tried below, receive "not valid month"
:
insert table1 (......) select ...., to_date(tochar(col6, '00') || to_char(col7, '00') || to_char(col8, '9999'), 'mm/dd/yyyy'), ..... table 2
does know how i'm after here , combine 3 specific varchar fields date insertion 1 field in table1?
if invalid values in columns zeros, said in comment, can use fixed value - either dummy date, or perhaps more reasonably leave new column null - case statement:
select ...., case when trim('0' col6) null or trim('0' col7) null or trim('0' col8) null null else to_date(lpad(col6, 2, '0') || lpad(col7, 2, '0') || lpad(col8, 4, '0'), 'mmddyyyy') end, ...
the trim removes zeros value , checks if left, catches both 0 , 00 (and 000 , 0000 col8) in 1 go.
the lpad left-pads value zeros, example value of 7 become 07 - important because need concatenated values match date format model, , without leading zeros you'd end confused , invalid results.
note i'm not using to_char @ all. since source columns strings, you're doing implicit conversion number, , explicit conversion string. fm modifier or trim have same effect lpad, doing more work.
as quick demo sample values in cte:
with t ( select '0' col6, '0' col7, '0' col8 dual union select '00', '00', '0000' dual union select '08', '31', '2008' dual union select '7', '4', '2012' dual ) select col6, col7, col8, case when trim('0' col6) null or trim('0' col7) null or trim('0' col8) null null else to_date(lpad(col6, 2, '0') || lpad(col7, 2, '0') || lpad(col8, 4, '0'), 'mmddyyyy') end my_date t; col6 col7 col8 my_date ---- ---- ---- ---------- 0 0 0 00 00 0000 08 31 2008 2008-08-31 7 4 2012 2012-07-04
if have other invalid values still trip on them - say, 09/31/2000, number of days in month wrong. isn't clear if might case data. if write function attempt convert whatever passed, , silently return null if it's invalid reason.
Comments
Post a Comment