Error converting data type varchar to numeric and varchar to datetime

Error converting data type varchar to numeric

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

declare @rsnMain numeric, @rsnAcnt numeric, @rsnRel numeric;
set @rsnMain = (select max(record_serial_number)
                from sdg_aaismain)
set @rsnAcnt = (select max(record_serial_number)
                from sdg_aaisacnt)
set @rsnRel = (select max(record_serial_number)
                from sdg_aaisrel)

insert into sdg_aaismain
(RECORD_SERIAL_NUMBER,
 AGENT_NUMBER,
 AGENT_STATUS_CODE,
 AGENT_STATUS_DESP,
 TRADE_NAME,
 GROUP_TYPE_1,
 MADE_ON_BEHALF,
 MADE_ON_BEHALF_DESP,
 LICENSE_RENEWAL_CODE,
 LICENSE_RENEW_DESP,
 ACTUAL_NO_STREET,
 ACTUAL_TOWN,
 ACTUAL_STATE,
 ZIP_CODE_PREFIX,
 ZIP_CODE_SUFFIX,
 CITY_CODE,
 DISTRICT_CODE,
 COUNTY_CODE,
 MUNICIPALITY_CODE,
 ACTUAL_DISTRICT,
 ACTUAL_COUNTY,
 ACTUAL_MUNICIPALITY,
 CONTACT_LAST_NAME,
 CONTACT_FIRST_NAME,
 STORE_OPEN_HOURS#1,
 STORE_OPEN_HOURS#2,
 STORE_OPEN_HOURS#3,
 STORE_OPEN_HOURS#4,
 STORE_OPEN_HOURS#5,
 STORE_OPEN_HOURS#6,
 STORE_OPEN_HOURS#7,
 STORE_CLOSE_HOURS#1,
 STORE_CLOSE_HOURS#2,
 STORE_CLOSE_HOURS#3,
 STORE_CLOSE_HOURS#4,
 STORE_CLOSE_HOURS#5,
 STORE_CLOSE_HOURS#6,
 STORE_CLOSE_HOURS#7,
 MAILING_ADDRESS,
 MAILING_CITY,
 MAILING_STATE,
 MAILING_ZIP_CODE_PREFIX,
 MAILING_ZIP_CODE_SUFFIX,
 CREATE_DATE,
 MODIFY_DATE,
 MODIFY_TIME,
 USER_ID,
 AGENT_TYPE,
 NAIC_CODE,
 CONTRACT_TYPE,
 LONGITUDE,
 LATITUDE,
 CORP_NAME,
 DSR,
 FEDTAX_NAME,
 FEDTAX_ID,
 TIN_TYPE,
 NAME_CNTL,
 ES_GROUPTYPE,
 ADDRESS2,
 --CORPORATION_NUMBER)
INSTALL_DATE)

select 
 @rsnMain + ROW_NUMBER() over (order by agent_number),
 AGENT_NUMBER,
 AGENT_STATUS_CODE,
 AGENT_STATUS_DESC,
 TRADE_NAME, 
 cast(right(CHAIN_PARENT_RETAIL_NO,3) as numeric(3,0)) as GROUP_TYPE_1,
 MADE_ON_BEHALF,
 MOB_DESC,
 LICENSE_RENEWAL_CODE,
 RENEWAL_STATUS_DESC,
 ACTUAL_NO_STREET,
 ACTUAL_TOWN, ACTUAL_STATE,
 cast(ZIP_PREFIX as numeric (5,0)) as ZIP_PREFIX,
 cast(ZIP_SUFFIX as numeric (4,0)) as ZIP_SUFFIX,
 CITY_CODE,
 cast(DISTRICT_CODE as numeric (3,0)) as DISTRICT_CODE,
 cast(COUNTY_CODE as numeric(3,0)) as COUNTY_CODE,
 MUNICIPALITY_CODE,
 ACTUAL_DISTRICT,
 ACTUAL_COUNTY,
 ACTUAL_MUNICIPALITY,
 CONTACT_LAST_NAME,
 CONTACT_FIRST_NAME,
 STORE_OPEN_HOURS#1,
 STORE_OPEN_HOURS#2,
 STORE_OPEN_HOURS#3,
 STORE_OPEN_HOURS#4,
 STORE_OPEN_HOURS#5,
 STORE_OPEN_HOURS#6,
 STORE_OPEN_HOURS#7,
 STORE_CLOSE_HOURS#1,
 STORE_CLOSE_HOURS#2,
 STORE_CLOSE_HOURS#3,
 STORE_CLOSE_HOURS#4,
 STORE_CLOSE_HOURS#5,
 STORE_CLOSE_HOURS#6,
 STORE_CLOSE_HOURS#7,
 MAILING_ADDRESS,
 MAILING_CITY,
 MAILING_STATE,
 cast(MAIL_ZIP_PREFIX as numeric (5,0)) as MAIL_ZIP_PREFIX,
 cast(MAIL_ZIP_SUFFIX as numeric (4,0)) as MAIL_ZIP_SUFFIX,
 convert(datetime, CREATE_DATE) as CREATE_DATE,
 convert(datetime, GetDate()) as MODIFY_DATE,
 replace(Convert (varchar(8), GetDate(), 108),':','') as MODIFY_TIME,
 'DAILY UPDATE',
 AGENT_TYPE,
 NAIC_CODE,
 cast(CONTRACT_TYPE as numeric (1,0)) as CONTRACT_TYPE,
 LONGITUDE,
 LATITUDE,
 CORP_NAME,
 DSR,
 FEDTAX_NAME,
 FEDTAX_ID,
 TIN_TYPE,
 NAME_CONTROL,
 cast(ES_GROUPTYPE as numeric (6,0)) as ES_GROUPTYPE,
 ADDRESS2,
 --cast(CORPORATION_NUMBER as numeric (11,0)),
 convert(datetime, INSTALL_date)
from sdg_tmp_aaismain as tmp
where NOT EXISTS(select NULL
                 from sdg_aaismain as dest
                 where tmp.agent_number = dest.agent_number 
                 ) 

The above code is a sample of a longer update statement. I have 2 columns giving me the "Error converting data type varchar to numeric" error. This is because there is character data for some records.

The out of range error is occurring for 4 different varchar columns with dates which all have '00000000' as a value for some of the rows.

How do I go about excluding the character data and the zeroes from my select query?

Thanks, Scott