SQL Error 17268 : Year out of range (Java/Spring)

Question

I'm facing an issue related to Oracle / Hibernate when I'm doing a simple SELECT on the database, and I can't find any useful information anywhere on this issue. The application uses Spring Data to create the queries on its own. The database is Oracle 11.02.0.4, and same version of JDBC driver.

SQL Error is 17268 : Year out of range.

I know the message is quite obvious, however when doing the query printed in the logs manually on the database, I have no issue and see that the year of the date field is well set to 2016...

I've tried with a Java object mapping those fields on Date and Timestamp, same error. Strange thing is that the problem suddenly appeared for the client, and I am unable to reproduce the issue on my own environment (client is running WebSphere 8.5.5).

Any pointers where this error might come from, or what should I investigate?

See stack trace below:

[11/14/16 12:05:03:370 CET] 000000d6 SystemOut     O 12:05:03.370 [WebContainer : 1] WARN  o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 17268, SQLState: 99999
[11/14/16 12:05:03:371 CET] 000000d6 SystemOut     O 12:05:03.371 [WebContainer : 1] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Year out of range.
[11/14/16 12:05:03:406 CET] 000000d6 ServletWrappe E com.ibm.ws.webcontainer.servlet.ServletWrapper service SRVE0014E: Uncaught service() exception root cause dispatcher: org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:979)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:858)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:575)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:843)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1232)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:781)
    at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:480)
    at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
    at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1114)
    at com.ibm.ws.webcontainer.webapp.WebApp.handleRequest(WebApp.java:3926)
    at com.ibm.ws.webcontainer.webapp.WebGroup.handleRequest(WebGroup.java:304)
    at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:1007)
    at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1817)
    at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:463)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:530)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:316)
    at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.ready(HttpInboundLink.java:287)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink.determineNextChannel(SSLConnectionLink.java:1049)
    at com.ibm.ws.ssl.channel.impl.SSLConnectionLink$MyReadCompletedCallback.complete(SSLConnectionLink.java:643)
    at com.ibm.ws.ssl.channel.impl.SSLReadServiceContext$SSLReadCompletedCallback.complete(SSLReadServiceContext.java:1818)
    at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
    at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
    at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
    at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
    at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
    at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
    at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
    at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1881)
Caused by: org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.GenericJDBCException: could not execute statement; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:415)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:418)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodIntercceptor.invoke(CrudMethodMetadataPostProcessor.java:111)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
    at com.sun.proxy.$Proxy83.findByEnvelopeIdAndStatusInAndCurrentVersionOrderByTecidAsc(Unknown Source)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:649)
    ...
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:717)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:653)
    ...
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:95)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:56)
    at java.lang.reflect.Method.invoke(Method.java:620)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:221)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:137)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:110)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:776)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:705)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:959)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:893)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:967)
    ... 29 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1763)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:458)
    at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:67)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:110)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:74)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:97)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:88)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:395)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:373)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 72 more
Caused by: org.hibernate.exception.GenericJDBCException: could not execute statement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:190)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3285)
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:3183)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3525)
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:158)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:453)
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:345)
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:350)
    at org.hibernate.event.internal.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:67)
    at org.hibernate.internal.SessionImpl.autoFlushIfRequired(SessionImpl.java:1187)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1253)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:573)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:449)
    ... 85 more
Caused by: java.sql.SQLException: Year out of range.
    at oracle.jdbc.driver.DateCommonBinder.setOracleCYMD(OraclePreparedStatement.java:19395)
    at oracle.jdbc.driver.TimestampBinder.bind(OraclePreparedStatement.java:19668)
    at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3166)
    at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2378)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3608)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3694)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1354)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteUpdate(WSJdbcPreparedStatement.java:1187)
    at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.executeUpdate(WSJdbcPreparedStatement.java:804)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:187)
    ... 98 more

Generated query:

select
    documentdb0_.tecid as tecid1_3_,
    documentdb0_.comments_nb as comments_nb2_3_,
    documentdb0_.content_path as content_path3_3_,
    documentdb0_.content_size as content_size4_3_,
    documentdb0_.creation_date as creation_date5_3_,
    documentdb0_.creator as creator6_3_,
    documentdb0_.current_version as current_version7_3_,
    documentdb0_.docfamily as docfamily8_3_,
    documentdb0_.doctype as doctype9_3_,
    documentdb0_.attached_document_id as attached_document10_3_,
    documentdb0_.envelope_id as envelope_id11_3_,
    documentdb0_.filename as filename12_3_,
    documentdb0_.hash as hash13_3_,
    documentdb0_.mime_type as mime_type14_3_,
    documentdb0_.modify_date as modify_date15_3_,
    documentdb0_.name as name16_3_,
    documentdb0_.number_of_pages as number_of_pages17_3_,
    documentdb0_.parent_id as parent_id18_3_,
    documentdb0_.root_id as root_id19_3_,
    documentdb0_.serie as serie20_3_,
    documentdb0_.status as status21_3_,
    documentdb0_.teclock as teclock22_3_,
    documentdb0_.version_id as version_id23_3_
from
    tb_document documentdb0_
where
    documentdb0_.envelope_id=44104
    and (documentdb0_.status in (‘TO_SIGN') )
    and documentdb0_.current_version=1
order by
    documentdb0_.tecid asc

Sample data retrieve by the query (manually) :

TECID1_3_;COMMENTS_NB2_3_;CONTENT_PATH3_3_;CONTENT_SIZE4_3_;CREATION_DATE5_3_;CREATOR6_3_;CURRENT_VERSION7_3_;DOCFAMILY8_3_;DOCTYPE9_3_;ATTACHED_DOCUMENT10_3_;ENVELOPE_ID11_3_;FILENAME12_3_;HASH13_3_;MIME_TYPE14_3_;MODIFY_DATE15_3_;NAME16_3_;NUMBER_OF_PAGES17_3_;PARENT_ID18_3_;ROOT_ID19_3_;SERIE20_3_;STATUS21_3_;TECLOCK22_3_;VERSION_ID23_3_
55054;0;/data/f5cbf3373b4d7bdc5abfc7df92334f54-20161024091158880.pdf;190354;24-oct-16;993513;1;329;203;;44104;22878767.pdf;f5cbf3373b4d7bdc5abfc7df92334f54;application/pdf;24-oct-16;CONSENT;2;;55054;11;TO_SIGN;3;1

can you post your sql query ?, and a tuple as an example o the data — Javier Toja, Nov 14 '16 at 11:31
and check this ftp://208.84.222.41/Oracle/Ora11g/jdbc/Readme.txt look for your ora code, it's looks like maybe you need to upgrade your orm version or check if those changes can affect you — Javier Toja, Nov 14 '16 at 11:40
I'd guess that you're implicitly converting somewhere and being affected by the databases NLS settings, but without the code it's impossible to say. — Ben, Nov 14 '16 at 13:17
@karelss It's done! Unfortunately, I don't have any ORA- code in the logs, only the SQL code. — Deathtiny, Nov 14 '16 at 13:42
@Ben: What do mean, maybe the date would be read in a wrongly local format? — Deathtiny, Nov 14 '16 at 13:43
@Deathtiny yes you have an ORA code "Error is 17268" this is equivalent to ORA-17268 — Javier Toja, Nov 14 '16 at 13:56
@Deathtiny NLS settings are somthing dependant to the database, and maybe your app and your database settings are not aligned talking about your timezone configuration — Javier Toja, Nov 14 '16 at 14:03
Are you sure this is the right query? It doesn't look like you've got any date or year parameters in it, unless one of the columns in your `WHERE` got changed to a date type unexpectedly. — Taylor, Nov 14 '16 at 14:25
@Taylor: Yes, `documentdb0_.creation_date as creation_date5_3_` and `documentdb0_.modify_date as modify_date15_3_` are the two date columns of this SELECT query (Oracle type : `DATE NOT NULL`) — Deathtiny, Nov 14 '16 at 14:28
A format error on `SELECT` is weird, value is already in DB, hence validated. Leads me to believe either a record is corrupted or a column in the `WHERE` column has had its datatype changed. Check the schema at the client, maybe a DBA fat-fingered something. Otherwise, they should review their data to ensure no records are malformed. — Taylor, Nov 14 '16 at 15:15

score 8 · Accepted Answer · answered Jan 06 '17 at 15:42

The issue was in fact strangely not at the line indicated by Java, which led me nowhere. In step-by-step debugging mode, I've stumbled upon an update query done that was causing an issue before the SELECT.

A date with year 10000 was trying to be introduced during UPDATE, when the maximum supported date by Oracle is 9999. This overflowing date was due to a deadline date read from another system, and defined to 31/12/9999 23:23:59: with a bad timezone it was read as 10000 January.

Yash · Answer 2 · 2020-04-13T12:41:58.730

SQL Error is 17268 : Year out of range.

Following error Year out of range will occur when we try to insert the Year value more than 9999. ORACLE supports till 9999 - 10000 leads Year out of range

For Util date their is limit for Year you can use any number as 777777 for year, But when you try to insert the same util date into the oracle server it leads to yearOutOfRange.

I have faced the same issue yearOutOfRange in multi-threading concept. When i try to convert String represented date to Sql date with timestamp. To reproduce the same you can the below code form MaintainEqualThreadsPatallel.loopFunction(...)

public static Timestamp getTimeStamp(Date date) { // yearOutOfRange
    //Date dbResponseTime = new java.util.Date();
    Timestamp timestamp = new java.sql.Timestamp(date.getTime());
    System.out.println("Timestamp :"+ timestamp);
    return timestamp;
}
private static final SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss");
public static Date stringToDate(String date) throws ParseException { // synchronized
    sdf.setTimeZone(TimeZone.getTimeZone("CET"));
    return sdf.parse(date);
}

Output:

getTimeStamp( stringToDate("20-Feb-2020 20:53:12") );

Timestamp :2020-02-20 20:53:12.0
Timestamp :1970-02-20 20:53:12.0
Timestamp :0020-02-20 02:53:12.0
Timestamp :20200-02-20 20:53:12.0  // yearOutOfRange
Timestamp :0020-02-20 02:53:12.0
Timestamp :2020-02-21 04:52:12.0
Timestamp :0012-02-21 20:53:12.0
java.lang.NumberFormatException: For input string: ""

From java.sql.Date source code we can observe that year supports till 9999 i.e., YYYY year code of date format.

Util Date to SQL Date:

java.util.Date utilDate = new java.util.Date();
java.sql.Date sqlDate = new java.sql.Date( utilDate.getTime() );

// SQL Source: new Date(date.getYear() - 1900, date.getMonthValue() -1, date.getDayOfMonth());

Oracle date functions: with some trials as follows

TO_DATE('08-FEB-10000', 'DD-MON-YYYY')
SQL Error: ORA-01830: date format picture ends before converting entire input string

TO_DATE('08-FEB-10000 13:30:12', 'DD-MON-YYYYY HH24:MI:SS')
SQL Error: ORA-01812: year may only be specified once

To avoid SQL Error Year out of range use any one of the following approach

SimpleDateFormat.parse(strDate) function call form synchronized block.
LocalDateTime.parse(strDate, DateTimeFormatter)

public static Timestamp getTimestamp(String strDate) {
    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss");
    LocalDateTime dateTime = LocalDateTime.parse(strDate, formatter);
                            //LocalDateTime.of(10000, Month.DECEMBER, 30, 12, 10, 05);

    Timestamp valueOf = java.sql.Timestamp.valueOf(dateTime);
    System.out.format("TimeStamp Local : [%-30s]\n", valueOf.toString());

    java.sql.Date sqlDate = new java.sql.Date( valueOf.getTime() );
    System.out.format("java.sql.Date : [%-30s]\n", sqlDate);

    return valueOf;

    /*LocalDateTime with = dateTime.with(TemporalAdjusters.lastInMonth(DayOfWeek.SUNDAY));
    int dayOfMonth = with.getDayOfMonth();
    System.out.println("Last Sunday of Month: "+dayOfMonth);*/
}

I recommend you don’t use `Timestamp`, `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, `SimpleDateFormat` in particular notoriously troublesome. Instead use `OffsetDateTime` or `LocalDateTime`, `LocalDate` and `DateTimeFormatter`, all from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Feb 24 '20 at 11:14

SQL Error 17268 : Year out of range (Java/Spring)

2 Answers2