JDBC dateformat not allowing zero values in date

Question

I have a SQL dataset with birthdate and deathdate of authors where the date sometimes contains zero values in the month and day area (example: 1947-00-00), this is done when the exact date of the birth or death is not known.

I am using R2RML to map this MySQL data to RDF data, and I use a precompiled java package as a R2RML processor. The problems occur when trying to convert the deathdate and birthdate of authors, as JDBC does not allow DATETIME with zerovalues, and I get this error message.

r2rml.engine.R2RMLException: MONTH
        at r2rml.database.Row.getObject(Row.java:41)
        at r2rml.model.TermMap.getValueForRDFTerm(TermMap.java:393)
        at r2rml.model.TermMap.generateRDFTerm(TermMap.java:305)
        at r2rml.model.TriplesMap.generateTriples(TriplesMap.java:189)
        at r2rml.model.R2RMLMapping.generateTriples(R2RMLMapping.java:36)
        at r2rml.engine.R2RMLProcessor.execute(R2RMLProcessor.java:50)
        at r2rml.Main.main(Main.java:56)
Caused by: java.sql.SQLException: MONTH
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:85)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getDate(ResultSetImpl.java:740)
        at com.mysql.cj.jdbc.result.ResultSetImpl.getObject(ResultSetImpl.java:1236)
        at r2rml.database.Row.getObject(Row.java:39)
        ... 6 more
Caused by: com.mysql.cj.exceptions.WrongArgumentException: MONTH

I have tried adding zeroDateTimeBehavior=convertToNull to the connection URL, but this seems to only work for complete zeroDateTime (0000-00-00), and I have not found any solutions where only the month and day are zero values.

Any suggestions to solve this? Is there anyway to solve this without changing the original dataset?

`where the date sometimes contains zero` ...these are not valid dates. Either store them as null, fix the source data, or create a year column for those year-only dates. — Tim Biegeleisen, Oct 05 '22 at 08:07

JDBC dateformat not allowing zero values in date

0 Answers0