Error (2665) when bulk loading null dates into Teradata database.

Question

When trying to write records with a null date using the Teradata Bulk Loader it returns a 2665 error and drops the record.  Null is a valid value...it is the default value, when not using the bulk loader this isn't an issue. I can replace the null with a data like 0001-01-01  but the business doesn't like that solution. I can come behind post load and update the 0001-01-01 values with null but that shouldn't be necessary.  Has anyone had and resolved a similar problem?

rqarnold · Answer

Thank you.

Yes, there are other date values being imported, not all are null...most are not null.  This is in a macro used to load multiple tables so any pre-sql would only be specific for one of the tables.  I just tried changing the _UT table date column to a varchar(10) type which moves the error to when the UT table is inserted into the desired table which currently is done by insert into tablename select * from tablename_ut.  It may require I have a specific insert statement for each table which I can only do (in the current configuration) when copying the data from the _UT table.

mceleavey · Answer

Hi @rqarnold ,

Are you loading in with other date values and only some are null?

If all are null then it doesn't know what data type to set the data on the import.

You can maybe try a pre-SQL statement:

case when date is null then ' ' else CAST(date as CHAR(10)) end
- or -
case when date is null then date'0001-01-01' else date end

I'm not sure if this will get around the lack of datatype or not...

M.