title: Why is my datetime parameter for a stored procedure being rejected?
body: <p>I’m working on a Java server app which uses Spring 3 and C3P0 to access a Microsoft SQL Server 2008 R2 database, using version 3.0 of Microsoft’s JDBC 4 driver.</p>
  
  <p>I have a sproc which has an input defined like so:</p>
  
  <pre><code>@modifiedAfter datetime = NULL
  </code></pre>
  
  <p>I’m using Spring to construct a call to this sproc.</p>
  
  <p>I’m constructing a <a href="http://static.springsource.org/spring/docs/3.0.x/javadoc-api/org/springframework/jdbc/core/namedparam/MapSqlParameterSource.html" rel="nofollow">MapSqlParameterSource</a> to contain my parameters:</p>
  
  <pre><code>MapSqlParameterSource in = new MapSqlParameterSource()
  in.addValue("modifiedAfter", "2011-01-01T00:00:00", Types.TIMESTAMP)
  </code></pre>
  
  <p>But when I execute the call:</p>
  
  <pre><code>this.sprocCall.execute(in);
  </code></pre>
  
  <p>I get this:</p>
  
  <blockquote>
    <p>com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string</p>
  </blockquote>
  
  <p>…and I have no idea why.</p>
  
  <p>I’ve tried a few variations on adding the parameter, such as passing it in as a <code>Date</code>, or specifying it as a <code>VARCHAR</code>, or not specifying the type — none of them work.</p>
  
  <p>I’m beginning to suspect that the problem might be related to Spring. I wrote a little Groovy script to try to isolate the problem, and this works just fine:</p>
  
  <pre><code>dt = new DateTime("2012-02-01T00:00:00") // Joda DateTime
  println sql.rows("exec spMySproc @modifiedAfter=${Sql.TIMESTAMP(dt.toString())}")
  </code></pre>
  
  <p>…but when I try the equivalent approach with <code>MapSqlParameterSource</code>, I get the above error.</p>
  
  <p>At this point, I’m stumped.</p>
  
  <p>Here’s the top of the stacktrace:</p>
  
  <pre><code>org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call spGetPoliciesCatalogPaged(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}]; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
      at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:97)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:952)
      at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:985)
      at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:368)
      at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute(AbstractJdbcCall.java:342)
      at org.springframework.jdbc.core.simple.SimpleJdbcCall.execute(SimpleJdbcCall.java:164)
  </code></pre>
  
  <p>… which is followed by some of my classes, then this:</p>
  
  <pre><code>Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
      at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4762)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1682)
      at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:955)
      at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)
      at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)
      at org.springframework.jdbc.core.JdbcTemplate.processResultSet(JdbcTemplate.java:1124)
      at org.springframework.jdbc.core.JdbcTemplate.extractReturnedResults(JdbcTemplate.java:1023)
      at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement(JdbcTemplate.java:995)
      at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:936)
      ... 68 more
  </code></pre>
  
  <p>I tried updating to the latest version of <code>spring-jdbc</code>, 3.1.1, but it did not help.</p>
  
  <p>I’d appreciate any help at all!</p>
  
  <p>Thanks,
  Avi</p>