I'm trying to update the endDate in a tuple, which is of type DATE. I have connected properly to the database through Java, and I'm able to run other queries. I'm 100% I am using the sql.date class and not the util.date. However, it still fails my constraint that I have set on the row of the table. Which is:

CHECK(JulianDay(startDate) <= JulianDay(endDate))

I'm pretty sure the problem is related to how I format the date that I'm trying to update. See 1 line in the main method. Here is a snippet of the code:

public static void main(String[] args) {
    updateProjectDeadline(1000, new Date(2026-11-31));
}

private static void updateProjectDeadline(int projectID, Date endDate) {
    Connection con = JDBCLogic.connect();
    PreparedStatement prepared;
    try {
        String sql = "UPDATE Project SET endDate = ? WHERE projectID = ?";
        prepared = con.prepareStatement(sql);
        prepared.setDate(1, endDate);
        prepared.setInt(2, projectID);
        prepared.execute();
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

The method works perfectly if I insert the date as a String object, however, then the constraint basically accepts anything. So I want to make it work with sql.date object.

I made it work by entering new Date(System.currentTimeMillis), however, this enters the date in as milliseconds in the database, I need the format as yyyy/mm/dd.

CodePudding user response：

Any text datetime format other than YYYY-MM-DD is useless when used with SQLite's datetime functions.
This makes your check constraint also useless.
You can check it yourself by executing:

SELECT julianday('2022/02/02');

The result is NULL, but:

SELECT julianday('2022-02-02');

returns the correct result.

So, first update the existing values of the 2 date columns in your table to the correct format:

UPDATE Project 
SET startdate = REPLACE(startdate, '/', '-')
    endDate = REPLACE(endDate, '/', '-');

Also, the check constraint can be simplified to just:

CHECK(startDate <= endDate)

CodePudding user response：

Numeric literals

One problem is that your code:

new Date(2026-11-31)

… is passing 1,984 to the constructor of Date. That is, you are using numeric literals with a minus sign to do math. Two thousand twenty-six minus eleven minus thirty-one equals 1,984.

You are passing that number to a constructor which takes a number if milliseconds since the epoch reference point of first moment of 1970 in UTC.

java.time

Looking at the bigger picture, another problem is that you are using terrible date-time classes that were years ago supplanted by the modern java.time classes. Sun, Oracle, and the JCP community gave up on these classes. I suggest you do the same.

For a date-only value, use LocalDate with a JDBC driver supporting JDBC 4.2 or later.

And, as commented, your intended date is invalid. There is no 31st of November. I’ll use the 30th instead.

LocalDate ld = LocalDate.parse( "2026-11-30" ) ;
myPreparedStatement.setObject( … , ld ) ;

This has been covered many many times already on Stack Overflow. Search to learn more.