Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 979
Codelearner Avatar asked Jun 12 '13 16:06

Codelearner


People also ask

How do you set a date to blank in SQL?

How to blank out a date field using SQL? "NULL" can be specified as a value in the Date field to get an empty/blank by using INSERT statement.


1 Answers

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) 
like image 118
LittleBobbyTables - Au Revoir Avatar answered Oct 04 '22 19:10

LittleBobbyTables - Au Revoir