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.

回答1:

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) 

回答2:

The epoch, or zero point, of SQL Server's calendar is 1 January 1900 at start-of-day (00:00:00.000).

The internal representation of a datetime consists of 2 32-bit signed integers. The high-order integer is the county of days since the epoch; the low-order integer is the count of milliseconds since start-of-day. Anything that results in the datetime value's representation being zero, such as:

select convert(datetime,0)
select convert(datetime,'')
select convert(datetime,0x0000000000000000)

will yield the epochal datetime value of 1 January 1900 00:00:00.000.

A null, however, is converted to … well … null, as the SQL standard requires that any expression or test involving a null (excepting the explicit test for nullity using is [not] null must itself evaluate to null.

OTOH, if you have SQL Server's options set up to treat null and nil strings pretty much interchangeably, you might well wind up with the epoch date.