SQL Server (Store Procedure) Mechanism Convert Empty String Date Parameter to Initial Date (1900-01-01)

It may you know about it but I would like to share this thing with you. We were working on one of project where we faced issue. we are storing data through sql server store procedure using date parameter. As per our logic, the date parameter has not date value it may be empty string. Whenever date parameter has empty string sql server store initial date(1900-01-01) in the date field. We checked our whole logic to find out how the initial date come if date parameter has an empty string. After debugging we found out it due to sql server mechanisum for convert empty string date value to initail date(1900-01-01). So make sure what exactly you want to store in table either selected date or date field with empty string. If you want to keep date field with empty string then you have to pass NULL if you haven’t date value. Hope it may be helpful you.

See, below store procedure.

[code:sql]

CREATE PROCEDURE [dbo].[usp_test_date]
(
@date date
)
AS
BEGIN
select @date;
END

[/code]

Run with two different way and see result.

1. exec dbo.[usp_test_date] @date = ”;

2. exec dbo.[usp_test_date] @date = NULL;