TSQL: Arithmetic overflow error converting expression to data type datetime

Question

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

Yes. Indeed. Its nvarchar in UAT env and Date in my own evn ;) — nishant
– nishant, Commented Jun 26, 2014 at 12:02
Well as you are using Datediff for datepart month chances of return value which is out of range for int is doubtful. IS this the only query which is getting fired or is it part of some big query. Also find out Max and Min dates of TX_DATE from TRANSACTIONS table. May be that helps. — Deepshikha
– Deepshikha, Commented Jun 26, 2014 at 12:31

Nikhil Talreja · Accepted Answer · 2014-06-26 12:03:40Z

1

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

answered Jun 26, 2014 at 12:03

Nikhil Talreja

2,7741 gold badge16 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

nishant Over a year ago

Indeed, TX_DATE is not of type date (checked after I posted the question). The thing that am still not clear about is why the query does not fail when run via Microsoft SQL Server Management Studio but fails when run via jdbc.

Nikhil Talreja Over a year ago

These are two different systems so it all depends on how the value of TX_DATE was interpreted.

Collectives™ on Stack Overflow

TSQL: Arithmetic overflow error converting expression to data type datetime

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related