Cannot insert NULL value in a DATETIME field in SQL server 2005 via C# code

I have a table in SQL server 2005 with field datatype DATETIME.

I am inserting a record to the above table via Visual C# .NET 2008 code.

Everything works but if the input is NULL then it inserts  "1900-01-01 00:00:00.000" this value in the DATETIME field. I want to insert nothing in that field when the input is NULL. How can I do it...

Here is my code:
 
String LEDate;
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, '" + LEDate + "'))";

Open in new window

sql server table field
toookiAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
toooki,

Try using DBNull.Value and since you are inserting into a column that is of data type DATETIME, you can drop the CONVERT() piece also.  Another method is to use NULLIF as it appears that LEDate is empty string and not null/nothing on the .NET side as CONVERT(DATETIME, NULL) == NULL; however, CONVERT(DATETIME, '') == '1900-01-01'.

String LEDate;
String insertStr = "INSERT INTO MyTab (LEDate) values (NULLIF('" + LEDate + "', ''))";

Open in new window


That trick should work out -- if not, then use IF/ELSE logic to sub in DBNull.Value when LEDate is ''.  Also, look at using parameter for SQL command.
0
 
mayank_joshiCommented:
you can do something like:-

if (LEDate==String.Empty)
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, '" + LEDate + "'))";
}
else
{
String insertStr = "INSERT INTO MyTab (LEDate) values (null)";
}

Open in new window

0
 
mayank_joshiConnect With a Mentor Commented:
sorry,correct code should be:-

if (LEDate==String.Empty)
{
String insertStr = "INSERT INTO MyTab (LEDate) values (null)";
}
else
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, '" + LEDate + "'))";

}

Open in new window

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Pratima PharandeConnect With a Mentor Commented:
if (LEDate==String.Empty)
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, '" + LEDate + "'))";
}
else
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, ' +  DBNull.Value + '))";
}

0
 
Pratima PharandeCommented:
if (LEDate==String.Empty)
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, ' +  DBNull.Value + '))";

}
else
{
String insertStr = "INSERT INTO MyTab (LEDate) values (" +
Convert(datetime, '" + LEDate + "'))";
}
0
 
dj_alikCommented:
PLEASE use Command patameters will  solve you many problems.
 String LEDate = null;
String insertStr = "INSERT INTO MyTab (LEDate) values (@LEDate ))";
//use DateTime .NET Object
sqlcmd.Parameters.Add("@LEDate ", SqlDbType.DateTime).Value = LEDate;
0
 
dj_alikCommented:
some correction:
String insertStr = "INSERT INTO MyTab (LEDate) values (@LEDate )";
String insertStr = "INSERT INTO MyTab (LEDate) values (@LEDate ))";
//use DateTime .NET Object
sqlcmd.Parameters.Add("@LEDate ", SqlDbType.DateTime).Value = LEDate; //DateTime .NET Object;
0
 
Kevin CrossChief Technology OfficerCommented:
I guess I missed a lot while I was typing.  I agree with all the above, but take a look at NULLIF trick.  Might come in useful for something else. ;)
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
Two problems.  
If the date is empty then your code should set the column to null - do NOT use Convert or other, that gives a default value - the one you are seeing in the table.  
The other problem is in the definition of the column in the table.  There you need to set the field to allow nulls.
0
 
mayank_joshiCommented:
another approach using parameterized queries :-

string constr = "";
// assign your connection string here
string LEDate = "";
// assign your datetime value here
object DateObj = null;
if (LEDate == string.Empty) {
	DateObj = DBNull.Value;
} else {
	DateObj = LEDate;
}
string insertStr = "INSERT INTO MyTab (LEDate) values (CONVERT(DATETIME,@leddate))";
System.Data.SqlClient.SqlConnection cn = new System.Data.SqlClient.SqlConnection(constr);
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(insertStr, cn);
cmd.Parameters.AddWithValue("@leddate", DateObj);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

Open in new window

0
 
toookiAuthor Commented:
Many thanks to you all.
That SQL server DATETIME field is nullable.
I am not using Coinvert....

I could not try all the above suggestions. But I tried with this: as Dj_A=alik said (Post 35752013)

cmdPBT = fconn.CreateCommand();
                cmdPBT.CommandText = "INSERT INTO MyTab (Name, LEDate, TADate) values ( " +
                    " @Name, @LEDate, @TADate " +
                    ")";
                cmdVRM = new SqlCommand(insertStrVRM, fconn);
                cmdPBT.Parameters.Add("@Name", SqlDbType.VarChar, 100);
                cmdPBT.Parameters.Add("@LEDate", SqlDbType.DateTime).Value = LEDate;
                cmdPBT.Parameters.Add("@TADate", SqlDbType.DateTime).Value = TADate;
cmdPBT.ExecuteNonQuery();

Not sure where I was wrong but it did not work.

I tried this  as mwvisa1 said (Post 35752017)

String LEDate;
String insertStr = "INSERT INTO MyTab (LEDate) values (NULLIF('" + LEDate + "', ''))";

It worked. It fixed the NULL value problem I posted. Thanks!

But I see a problem .
The data I am inserting in the LEDate field is like '01/10/2011' it does not have a time part. So after inserting it is showing as the attached screenshot. "2011-01-10 00:00:00.000"
How can I set it to show "2011-01-10".
Should I change the datatype of the SQL server table field?
. op
0
 
toookiAuthor Commented:
I tried changing LEDate to "Date" data type but the table creation script gave error. Said no such data type...Is DateTime the only option..?
0
 
dj_alikCommented:
try:  
cmdPBT.Parameters.Add("@LEDate").Value = LEDate;
                cmdPBT.Parameters.Add("@TADate).Value = TADate;
0
 
AndyAinscowFreelance programmer / ConsultantCommented:
>>Without a time part it shows 00:00:00
That is how the DateTime is stored internally, the default is a time of 00:00:00 and has no effect on what you are doing.
Users should not be looking/working directly in the table in the database - you can display the information in any way (just as a date) you wish on the GUI.
0
 
mayank_joshiCommented:
datetime or smalldatetime  are options for dates in sql 2005.
 sql 2008 has a date field which does not save time.

for displaying only date in sql you can use:-

 select CONVERT(varchar,LEDDate,23)

Open in new window


for other formats visit:-
http://www.sqlusa.com/bestpractices2005/centurydateformat/
0
 
Alpesh PatelAssistant ConsultantCommented:
Please send date in SQL compitible format

(YYYY-MM-DD HH:MM:SS)
0
 
käµfm³d 👽Commented:
But the input is not null....  it is an empty string  = )

When inserting to a DateTime field, if you pass an empty string, it gets converted at the DB to the minimum date for the system. You need to pass a database version of NULL to the DB. Use DBNull.Value for this (as described by mayank_joshi in http:#35752175).
0
 
prajapati84Commented:
As mayank_joshi said, you can use the date conversion at sql side or also you can do it at GUI (application) side.

sql side: select CONVERT(varchar(10),LEDDate,103)
gui side: string led_date = Convert.ToDateTime(column("LEDDate")).ToString("mm/DD/yyyy");


0
 
toookiAuthor Commented:
Ok, thank you all for help explaining the  DateTime data type. I understand now.

However, my main problem was with the null values for DateTime fields. And I got the solution here.

Thank you all.
0
All Courses

From novice to tech pro — start learning today.