Let see the example:
DECLARE @dates TABLE(aDate DATETIME NULL)
DECLARE @myDate DATETIME
SET @myDate='2013-01-01'
INSERT INTO @dates (aDate)
VALUES(@myDate)
SET @myDate=NULL
INSERT INTO @dates (aDate)
VALUES(@myDate)
In above example,
@dates
table has defined
aDate
field which can store
NULL
values. So, when you try to add nullable values, table "accept" it.
Change the definition of your table to:
DECLARE @dates TABLE(aDate DATETIME NOT NULL)
and try to add NULL value.
What would happen? Error occurs:
Msg 515, Level 16, State 2, Line 12<br />
Cannot insert the value NULL into column 'aDate', table '@dates'; column does not allow nulls. INSERT fails.
If you want to add default date in case of NULL, create stored procedure like this:
CREATE PROCEDURE AddNewDefaultDate
@myDate DATETIME NULL
BEGIN
INSERT INTO YourTable (DateColumn, Column1, Column2, Column3,... ColumnN)
VALUES(COALESCE(@myDate, '1900-01-01'), 'a', 1,... 3)
END