How can I map a DATETIME column to Date object using JPA and DB2

Question

I'm trying to query a DB2 database and mapping a DATEIME colum to Date using Java but I'm getting this error:

DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.21.29

Short Description: THE STRING REPRESENTATION OF A DATETIME VALUE IS NOT A VALID DATETIME VALUE C

The string representation of a datetime is not in the acceptable range or is not in the correct format. The proper ranges for datetime values are as follows: Table 2. Range of datetime values Datetime Numeric Range Years 0001 to 9999 Months 1 to 12 Days April, June, September, November (months 4, 6, 9, 11) 1 to 30 February (month 2) 1 to 28 (Leap year 1 to 29) January, March, May, July, August, October, December (months 1, 3, 5, 7, 8, 10, 12) 1 to 31 Hours 0 to 24 (If hour is 24, other parts of time values are zeroes. If hour is USA, maximum hour is 12.) Minutes 0 to 59 Seconds 0 to 59 Microseconds 0 to 999999 System action: The statement cannot be executed. Programmer response: Check whether the value is within the valid range and is in the proper format. Refer to Chapter 2 of DB2 SQL Reference for information on string data formats.

First I'm convert a LocalDate to Date

public static Date convertLocalDateToDate(LocalDate date) {
    return  Date.from(date.atStartOfDay(ZoneId.systemDefault()).toInstant());
}

Second I'm using a Specification API

public static Specification<MyEntity> dataUse(Date dataUse){
    return (root, query, builder) -> builder.equal(root.get("myDate"), dataUse);
}

My field within the Entity is map as

@Column(name="DT_EX_MOVI_IN")
@Temporal(TemporalType.DATE)
private Date myDate;

And final when I try to execute the query

repository.findAll(query);

I get the error error DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.21.29

Someone Could help me?

Thanks

In My DataBase the Date that I want to compare was created as Date — Guilherme Rosa dos Santos
Are you using the java.sql.Date class or java.util.Date ?, Why don't you map the column directly to a LocalDate ? — eHayik
The problemn isn't when I get data of DB for my Object Java. The problem is when I execute the query. The Problem: MY DB2 has a collum map as DATE, not DATETIME. When I try to execute query comparing with Object JAVA DATE with collumn in my DB2 with DATETIME is OK I can return data but when I try to execute my Object JAVA DATE with collumn in my DB2 with DATE I get error DB2 SQL Error: SQLCODE=-181, SQLSTATE=22007, SQLERRMC=null, DRIVER=4.21.29. I have alredy tried to execute query with other types Object Java of type DATE such as DATE, DateLocal, LocalDateTime but I haven't success. — Guilherme Rosa dos Santos

Guilherme Rosa dos Santos Guilherme Rosa dos Santos · Accepted Answer · 2019-07-19T11:42:00

I solved my problem. I read documentation of DB2 https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/java/src/tpc/imjcc_rjvjdata.html

And my solution was map my object with java.sql.Date.

Thank you very much for all that tried help me!

How can I map a DATETIME column to Date object using JPA and DB2

1 Answers