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

Question

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.

Have you checked whether there is a not null constraint on the date column? — user359040
– user359040, Commented Jan 19, 2012 at 14:46

Ajay Takur · Accepted Answer · 2016-08-06 06:49:03Z

22

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);

edited Aug 6, 2016 at 6:49

Ajay Takur

6,2245 gold badges45 silver badges58 bronze badges

answered Jan 19, 2012 at 14:54

narek.gevorgyan

4,1856 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kane Over a year ago

perhaps it's changed, but the sqlType parameter should now be java.sql.Types.DATE

David M · Accepted Answer · 2012-01-19 15:05:45Z

3

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.

edited Jan 19, 2012 at 15:05

answered Jan 19, 2012 at 14:43

David M

73.2k13 gold badges164 silver badges187 bronze badges

4 Comments

meebee Over a year ago

i just edited my post to show more of my code. thank you, is there a certain format for a Date variable?

David M Over a year ago

I've expanded on my answer as a result.

meebee Over a year ago

thank you, i will try to study the use of the Date as i have not used it before.

Stephen Connolly Over a year ago

This is not strictly an answer, should have been a comment on the original question.

Jon Strayer · Accepted Answer · 2012-01-19 14:43:26Z

1

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

answered Jan 19, 2012 at 14:43

Jon Strayer

2,0982 gold badges20 silver badges41 bronze badges

1 Comment

Stephen Connolly Over a year ago

setDate with a null value passed through does not work for all JDBC drivers. the spec compliant way is to call setNull(index,Types.Date)

meebee · Accepted Answer · 2012-01-22 16:43:54Z

0

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 + "'";

answered Jan 22, 2012 at 16:43

meebee

2611 gold badge7 silver badges18 bronze badges

1 Comment

Stephen Connolly Over a year ago

This is a very bad "solution" the correct way is to use the ? parameter holder and call setNull on the prepared statement as indicated by @narek.gevorgyan

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related