U r correct 'A character string failed conversion to a numeric vale'. But why? Its working in ODBC and why not in Teradata.Net
I guess .net is handling conversions differently as ODBC.
But as you have a date column you should be save if you convert your string explicitly into a date in the insert. This will prevent you also from errors or the need to change code in case someone decides to change the date format in the table.
INSERT INTO temp VALUES('a','b',cast('01012000' as date format'ddmmyyyy'),cast('05012000' as date format'ddmmyyyy'));