meebee meebee - 4 months ago 25
SQL Question

how to insert null value into datetime column in sql with java

Does anyone know how to set a null value into a datetime column in a database in sql? I tried setting the 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
.

Also I tried setting my string to no value by simply declaring it (String date;). however this would just end up setting the date as January 1, 1900.

I used 3 dropdown boxes for the user of the program to choose the month, day, and year each.

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;


}


Lastly, I would give newdateprocessed.newdate() to the column in my database. It would work fine if the user would choose a date, but if they did not choose a date that I would get an error, which is why I am trying to find a way to be able to put a null value.

Answer

i figured out what to do. the query that i used for updating my database was originally this:

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

i took out the single quotation marks in the query and changed the query to this:

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

and in my code for setting the value of newdate in my DateNew function, if it was null i set it to be:

newdate = "NULL";

and if it wasn't i set it to be:

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