How to insert null value in datetime column in sql with java

Question

Does anyone know how to set a value to zero in a datetime column in a database in sql? I tried to set String as null: String date = null; but I would get this error:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Conversion failed when converting datetime from character string .

I also tried setting my string without a value by simply declaring it (String date;). however, this would only finish setting the date as January 1, 1900.

I used 3 drop-down windows for the user of the program to select the month, day and year.

 DateNew newdateprocessed; int b, c; String d; //First I took the values from each drop down box. b = Date_Processed_Month_Mod.getSelectedIndex(); c = Date_Processed_Day_Mod.getSelectedIndex(); d = Date_Processed_Year_Mod.getSelectedItem().toString(); newdateprocessed = new DateNew(b, c, d); //Then I used these values in a separate function to put them all together as just one string public class DateNew extends Object { String newdate; String monthword, newdateword; int check=0; public DateNew (int month, int day, String year) { //newdate = ""+ month + "/" + day + "/" + year; if (month == 0 | day == 0 | year.equals("Year")) { newdate=null; check = 1; } else { switch (month) { case 1: monthword="January"; break; case 2: monthword="February"; break; case 3: monthword="March"; break; case 4: monthword="April"; break; case 5: monthword="May"; break; case 6: monthword="June"; break; case 7: monthword="July"; break; case 8: monthword="August"; break; case 9: monthword="September"; break; case 10: monthword="October"; break; case 11: monthword="November"; break; case 12: monthword="December"; break; } newdateword= monthword + " " + day + ", " + year; newdate = ""+ month + "/" + day + "/" + year; } check=month; } public String newdate() { return newdate; } public int newdatecheck(){ return check; } public String newdateword(){ return newdateword; } 

Finally, I would give newdateprocessed.newdate () to a column in my database. It will work fine if the user selects a date, but if they did not select a date on which I would get an error, that’s why I am trying to find a way to set the value to zero.

Answer 1

I understood what to do. The query I used to update my database was originally:

 String query = "UPDATE Licenses Set Date_Processed = '" + newdateprocessed.newdate() + "'"; 

I took out single quotes in the request and changed the request to this:

 String query = "UPDATE Licenses Set Date_Processed =" + newdateprocessed.newdate(); 

and in my code to set the newdate value in my DateNew function, if it was null, I set it as:

 newdate = "NULL"; 

and if I did not install it:

 newdate = "'" + month + "/" + day + "/" + year + "'"; 

Answer 2

Try to call

 setNull(index,java.sql.Types.DATE) 

for instance

 String query = "UPDATE Licenses Set Date_Processed = ?"; PreparedStatement queryStatement = (PreparedStatement)connection.prepareStatement(sql); queryStatement.setNull(1, java.sql.Types.Date); 

Answer 3

Why line? If this is a datetime column, you can try:

 Date date = null; 

It seems you are passing the string to the data access code that is stored in the datetime column, so you rely on the DBMS to interpret the value of the string and convert it to date-time. It is not possible to handle a null value, so you will have to change the way you do this to actually pass a compatible data type, such as a Date instance.

Answer 4

Try using the setDate method (and passing to null) instead of the setString method.