Cannot insert null into datetime in sql server

问题:

I am trying to insert null with my query into a datetime column which allows null. My query works fine (I think). But its putting 1900-01-01 00:00:00.000 instead of null into the datetime column. Why is this happening and how do I fix it ?

I created my own table to test this and nulling is NOT a problem there. But, it is a problem in another database. I think it must be something to do with the other database.

回答1:

To see all the triggers in the database, use the following query:

select 
    t.name as [TableName], 
    tr.name as [TriggerName], 
    m.[definition]
from sys.triggers tr
join sys.sql_modules m on m.object_id = tr.object_id
join sys.tables t on t.object_id = tr.parent_id

To see all the default constraints, use the following query:

select 
    t.name as [TableName],  
    c.name as [ColumnName], 
    dc.[name] as [ConstraintName], 
    dc.[definition] 
from sys.tables t
join sys.columns c on c.object_id = t.object_id
join sys.objects do on do.object_id = c.default_object_id
join sys.default_constraints dc on dc.object_id= do.object_id

回答2:

When inserting using INSERT query, don't specify the column name and don't give any value. That should insert null in the field.

For example, If Entry_Date is my nullable datetime column in abc table, then my insert statement would be like:

Insert into abc (Entry_Id, Entry_Value) values (1,1000);

By not mentioning the column, it should have null in it. Hope it helps.

回答3:

If your insert statement is O.K., then the only reason would be a trigger that alters the value of the insert. (Providing there isn't a bug on your SQL Server. :-) )

So check if your table you're inserting into does have triggers and what they do.

To see the list of triggers either select from sys.triggers in the DB where is the table or in SQL Server Management Studio in the Object Explorer go to the table and then expand it / Triggers - then you can check each trigger. You need to check INSTEAD OF triggers. But you might have a look also onto AFTER triggers if INSTEAD OF triggers don't cause this.

The other option is that the insert statement has a bug and the column defaults to 1900. In that case, are you sure you insert into the column you want ? Do you use INSERT Table(List of columns) Values and the order of columns and order of values is correct ?