Can I set a date field in DB2 to null

Question

Why does DB2 write 01/01/0001 in the date field when I set it to null?

Can I set that field to NULL. When db2 set it to 01/01/0001 my ado recordset blows up because it is not a valid date.

Am I doing something wrong?

Answer 1

You can of course set a date column to a NULL value. How do you do that?

Either the client you use is messing with you, or there is a trigger on the table that changes NULL values in that column to the value you see.

Answer 2

1. Yes you can insert nulls into a date column. Below is the syntax that works to insert a null value(depending on version). Timestamp is the column type so you can interchange it with date. Depending on your DB2 version you might have a different method to do it. This will not work if the field specification of the datetime field was not set to allow nulls.

   INSERT INTO [Table Name](col1, col2, col3)SELECT Value1, Value2, cast(NULL as timestamp) FROM [Source Table]

2. The reason you're seeing 01/01/0001 is because DB2 is attempting to convert the null to a value and 01/01/0001 is the default for a date field.