Search code examples
sqlsql-server-2005

SQL Server inserting Date as 1/1/1900

I am having ASP Classic page to insert record in table. I am using form element for 5 fields viz. [ABC] ([code], [updatedate], [flag], [Mfdate]. And using the date control to get user selected date in Mdate when user does not select the Mdate, the query that it is getting formed in my ASP page is as below

INSERT INTO [ABC] ([code], [updatedate], [flag], [Mfdate]) 
VALUES('203 ', '6/12/2013', 'N/A', '')

When it is run in SQL Server it is inserting date 1/1/1900 for Mfdate but User has not selected any value.

Why is it happening like this?

The data type for Mfdate is datetime.

Solution

  • You have not given it as null, you're trying to insert an empty string (''). You need:

    INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '6/12/2013','N/A', NULL)

    Although really, if you're going to be inserting dates, best to insert them in YYYYMMDD format, as:

    INSERT INTO [ABC] ([code],[updatedate],[flag],[Mfdate]) 
VALUES ('203', '20130612','N/A', NULL)