Amirhosein
Amirhosein

Reputation: 4446

How update a null date field in SQLite

In a SQLite table I have two fields END_DATE and START_DATE. At fist run END_DATE is empty and null. At second run I want to update previous record where END_DATE is null with current time and create new record but it returns error:

     try {
                updateBuilder.where().eq("END_DATE", null);
                updateBuilder.updateColumnValue("END_DATE", currentTime);
                updateBuilder.update();

                   } catch (SQLException e) {
                e.printStackTrace();
            }

   appUsageLog.setStartDate(currentTime);
            appUsageLog.setPackageName(topPackageName);
            appUsageLog.setEndDate(null);
            appUsageLog.setUser(member);
            try {
                appUsageDao.create(appUsageLog);

            } catch (SQLException e) {
                e.printStackTrace();
            }

error:

java.sql.SQLException: argument for 'EndDate' is null

Upvotes: 1

Views: 642

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

Use the isNull function to check for NULL database values:

updateBuilder.where().isNull("END_DATE");
updateBuilder.updateColumnValue("END_DATE", currentTime);
updateBuilder.update();

As the documentation for isNull states, we cannot use equality = to check for NULL values in SQLite (or really any flavor of SQL):

Add a 'IS NULL' clause so the column must be null. '=' NULL does not work.

Upvotes: 1

Related Questions