sql - Netezza INSERT - ignore invalid dates? -

is there option or setting tell netezza ignore invalid dates ? entire insert fails in example below because 3rd row of source table has invalid date. expect offending row may skipped, insert 0 rows seems tad extreme.

i tried following, failed well. alternative parsing source date , verifying each component validity ( including days/month, leap years etc.. )

insert db.test ( cmclmn, effdt, efftm )          select cmclmn, case when to_date(effdt,'yyyymmdd') null                           null                           else to_date(effdt,'yyyymmdd') end,                 cast(lpad(efftm,6,0) time) efftm           db.test_src           ; 

entire script:

create table db.test (         cmclmn integer,         effdt   date,         efftm   time         ) distribute on ( cmclmn );  drop table db.test_src; create table db.test_src (         cmclmn integer,         effdt integer,         efftm integer)         distribute on ( cmclmn );  insert db.test_src ( cmclmn, effdt, efftm ) values  ( 1,20140120, 102000);  insert db.test_src ( cmclmn, effdt, efftm ) values  ( 2,20140121, 231212);  insert db.test_src ( cmclmn, effdt, efftm ) values  ( 3,0,111111 );  insert db.test_src ( cmclmn, effdt, efftm ) values  ( 4,20140123 ,90909 );   insert db.test ( cmclmn, effdt, efftm )          select cmclmn, to_date(effdt,'yyyymmdd'), cast(lpad(efftm,6,0) time) efftm           db.test_src           ; 

you can create user defined function full test. case, perhaps simpler things do:

insert db.test ( cmclmn, effdt, efftm )      select cmclmn, to_date(effdt,'yyyymmdd'), cast(lpad(efftm,6,0) time) efftm     db.test_src     effdt between 20010000 , 20150000 ,           effdt % 10000 between 0101 , 1231 ,           effdt between 01 , 31; 

i'm not sure if netezza ever optimizes queries to_date() might run before filter. if so, use case well:

insert db.test ( cmclmn, effdt, efftm )      select cmclmn,            (case when effdt between 20010000 , 20150000 ,                       effdt % 10000 between 0101 , 1231 ,                       effdt % 100 between 01 , 31                  to_date(effdt,'yyyymmdd')             end),            cast(lpad(efftm,6,0) time) efftm     db.test_src     effdt between 20010000 , 20150000 ,           effdt % 10000 between 0101 , 1231 ,           effdt % 100 between 01 , 31; 

the date checking isn't perfect, might sufficient purposes.