Hibernate Query Error - Date Formatting

Posted on 2008-11-03
Last Modified: 2012-05-05
This is my query:

Select id, last_name, first_name from info where in_time >= '2008-03-12 00:00 AM' and in_time <='2008-12-18 00:00 AM'

It is simple and it worked before.  We have several people working on this project and I cannot determine what caused it to stop working.  I can run this fine in query analyzer, yet in my application, I get the following error.

2008-11-03 17:10:45,906 [JDBCExceptionReporter] ERROR - Syntax error converting datetime from character string.
2008-11-03 17:10:45,921 [InfoHandler] ERROR - could not execute query
2008-11-03 17:10:45,921 [InfoHandler] ERROR - Initiating rollback
2008-11-03 17:10:45,921 [InfoHandler] ERROR - could not execute query
2008-11-03 17:10:45,921 [InfoHandler] ERROR - org.hibernate.exception.DataException: could not execute query
      at org.hibernate.exception.SQLStateConverter.convert(
      at org.hibernate.exception.JDBCExceptionHelper.convert(
      at org.hibernate.loader.Loader.doList(
      at org.hibernate.loader.Loader.listIgnoreQueryCache(
      at org.hibernate.loader.Loader.list(
      at org.hibernate.loader.custom.CustomLoader.list(
      at org.hibernate.impl.SessionImpl.listCustomQuery(
      at org.hibernate.impl.SQLQueryImpl.list(
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(
      at java.lang.reflect.Method.invoke(
      at org.apache.struts.actions.DispatchAction.dispatchMethod(
      at org.apache.struts.actions.DispatchAction.execute(
      at org.apache.struts.action.RequestProcessor.processActionPerform(
      at org.apache.struts.action.RequestProcessor.process(
      at org.apache.struts.action.ActionServlet.process(
      at org.apache.struts.action.ActionServlet.doPost(
      at javax.servlet.http.HttpServlet.service(
      at javax.servlet.http.HttpServlet.service(
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(
      at org.apache.catalina.core.StandardWrapperValve.invoke(
      at org.apache.catalina.core.StandardValveContext.invokeNext(
      at org.apache.catalina.core.StandardPipeline.invoke(
      at org.apache.catalina.core.StandardContextValve.invokeInternal(
      at org.apache.catalina.core.StandardContextValve.invoke(
      at org.apache.catalina.core.StandardValveContext.invokeNext(
      at org.apache.catalina.core.StandardPipeline.invoke(
      at org.apache.catalina.core.StandardHostValve.invoke(
      at org.apache.catalina.core.StandardValveContext.invokeNext(
      at org.apache.catalina.valves.ErrorReportValve.invoke(
      at org.apache.catalina.core.StandardValveContext.invokeNext(
      at org.apache.catalina.core.StandardPipeline.invoke(
      at org.apache.catalina.core.StandardEngineValve.invoke(
      at org.apache.catalina.core.StandardValveContext.invokeNext(
      at org.apache.catalina.core.StandardPipeline.invoke(
      at org.apache.catalina.core.ContainerBase.invoke(
      at org.apache.coyote.tomcat5.CoyoteAdapter.service(
      at org.apache.coyote.http11.Http11Processor.process(
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(
      at org.apache.tomcat.util.threads.ThreadPool$
Caused by: java.sql.SQLException: Syntax error converting datetime from character string.
      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(
      at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(
      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(
      at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(
      at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(
      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(
      at org.hibernate.jdbc.AbstractBatcher.getResultSet(
      at org.hibernate.loader.Loader.getResultSet(
      at org.hibernate.loader.Loader.doQuery(
      at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
      at org.hibernate.loader.Loader.doList(
      ... 43 more

Question by:infrasafe
    LVL 39

    Expert Comment

    Your datetime format may be interpreting incorrectly.  Try explicitly providing the date format.

    Select id, last_name, first_name from info 
    where in_time >= convert(datetime, '2008-03-12 00:00 AM', 20)
    and in_time <= convert(datetime, '2008-12-18 00:00 AM', 20)

    Open in new window


    Author Comment

    I tried that before, thinking that was the problem, but it did not work.  However, I did use a different datetime style code than what you used.  I cut and pasted what you provided and I received the same error.  I cannot make any sense out of it.
    LVL 39

    Expert Comment

    Then the query you are executing is not passing the dates in the same format that you are posting.  I would suggest running SQL profiler to capture the ACTUAL call.

    Because if you run this query, you should see that the conversion IS correct.
    select convert(datetime, '2008-03-12 00:00 AM', 20), convert(datetime, '2008-12-18 00:00 AM', 20)

    Accepted Solution

    I see that it is correct and I am not disputing that.  I am unsure why hibernate is rejecting it as a valid datetime.  I have have a similar query that is working and the date is formatted differently.  I changed my query to format the date like that and now it works.  I am not sure what changed because that date format worked previously.  Fortunately, that format will work for me.  I am not sure why it doesn't like minutes but hours will work so I guess my problem is fixed.

    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH a");

    Select id, last_name, first_name from info where in_time >= '2008-03-12 00 AM' and in_time <= '2008-12-18 00 AM'
    LVL 39

    Expert Comment

    I still think you MAY run into problems because '2008-12-18 00 AM' is not a recognized SQL Server DATE format.  If you are passing a string containing a date into SQL server, you should always specify the format.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now