How to insert NULL into the DATETIME coulmn instead 1900-01-01 00:00:00.000 in SQL Server

I am new to SQL Server here I am trying to insert date into the column which has DATETIME as datatype .

When I pass a datetime to the stored procedure from API it insert the values which I have passed. But If I pass an empty string " ", it inserts a default value of 1900-01-01 00:00:00.000. But I want to insert NULL if the data is " ".

I googled a lot but I can’t find a solution for this .

@DATEVAL1 DATETIME = '',
@DATEVAL1 DATETIME = ''

INSERT INTO TABLE (mDate1, mDate2)
VALUES (@DATEVAL1, @DATEVAL2);

This is the code I have in my stored procedure.

Answer

When you set a datetime variable to an empty string an implicit conversion happens. And an empty string converts implicitly to 1900-01-01. Either just declare the variable and don’t assign them a value or explicitly set them to NULL.

@DATEVAL1 DATETIME,
@DATEVAL2 DATETIME = NULL

Or you could use NULLIF. Since you are receiving these values from an API you would most likely have to use NULLIF. Otherwise a change to the API call would be required to send in a NULL instead of an empty string.

Values( NULLIF(@DATEVAL1, '')