Menu
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.
201
"NULL" can be specified as a value in the Date field to get an empty/blank by using INSERT statement. Example: CREATE table test1 (col1 date); INSERT into test1 values (NULL);
To permit a TIMESTAMP column to contain NULL , explicitly declare it with the NULL attribute. In this case, the default value also becomes NULL unless overridden with a DEFAULT clause that specifies a different default value. DEFAULT NULL can be used to explicitly specify NULL as the default value.
You also can specify the NULL keyword in the VALUES clause to indicate that a column should be assigned a NULL value. The following example inserts values into three columns of the orders table: INSERT INTO orders (orders_num, order_date, customer_num) VALUES (0, NULL, 123);
A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0 , which in SQL Server is implicitly an integer representing the number of days since 1900-01-01 .
Try to call
setNull(index,java.sql.Types.DATE)
For example
String query = "UPDATE Licenses Set Date_Processed = ?";
PreparedStatement queryStatement = (PreparedStatement)connection.prepareStatement(sql);
queryStatement.setNull(1, java.sql.Types.Date);
Why a string? If it's a datetime column, you could try:
Date date = null;
You seem to be passing a string to your data access code, which is being stored in a datetime column, so you are relying on the DBMS to interpret the string value and convert it to a datetime. There is no way this can handle a null value, so you are going to have to change the way you are doing this to actually pass a compatible data type like a Date instance.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
