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

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -