CODEWITHSUNDEEP
sqlt-sqljdbc
nishant
nishant

Reputation: 985

TSQL: Arithmetic overflow error converting expression to data type datetime

I am trying to run this simple query to get sum of all amounts for last year and getting error. Can someone shed some light on this please. I am not doing any casting and TX_DATE column is of type date.

SELECT SUM(AMOUNT) as TOTAL
FROM
    TRANSACTIONS
WHERE
    ID = '12345'
    AND TYPE = 'Amount'
    AND DATEDIFF(MONTH,TX_DATE,GETDATE()) <= 12

Error: 

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting expression to data type datetime.
    at   com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
    at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:91)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:1)
    at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:446)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)
    ... 50 more

Upvotes: 0

Views: 1821

Answers (1)

Nikhil Talreja
Nikhil Talreja

Reputation: 2774

Check type of TX_DATE. It seems its not a date.

Upvotes: 1

Related Questions