CODEWITHSUNDEEP
sqljdbc
user3863488
user3863488

Reputation: 227

Arithmetic overflow error converting expression to data type datetime while querying SQL

I am facing issue while querying for some data from SQL. The column datatype is varchar which has datetime stamp as part of its name, like DUMMY2_20140713.pdf.

Want to search for files between week date duration.so trying to convert part of file name to date as below:

select file_name,file_name 
from t_pdf_weekly_violation 
where CONVERT(datetime, SUBSTRING(file_name, CHARINDEX('_', file_name) + 1, 8), 112) between 20150506 and 20150513;

But it is throwing exception..Please find stack trace below:

com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting expression to data type datetime. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:196) at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4700) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1683) at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:956) at databaseconnection.ConnectionURL.main(ConnectionURL.java:36)

Have any one idea on this?

Thanks in advance

Upvotes: 0

Views: 2001

Answers (2)

user3863488
user3863488

Reputation: 227

It is working when I changed return type datetime to char, like

SELECT file_name,file_name 
    FROM t_pdf_weekly_violation 
    WHERE CONVERT(char(8),SUBSTRING(file_name, CHARINDEX('_', file_name) + 1, 8),112) 
         between CONVERT(char(8),'20150506',112) and CONVERT(char(8),'20150510',112);

Upvotes: 0

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

There is no need to convert substring to Datetime, just remove CONVERT(datetime

SELECT file_name,file_name 
FROM t_pdf_weekly_violation 
WHERE SUBSTRING(file_name, CHARINDEX('_', file_name) + 1, 8) between 20150506 and 20150513;

Sample SQL FIDDLE

Alternate Solution

If you want to use CONVERT(datetime, then you have to convert your all dates 

SELECT file_name,file_name 
    FROM t_pdf_weekly_violation 
    WHERE CONVERT(datetime,SUBSTRING(file_name, CHARINDEX('_', file_name) + 1, 8),112) 
         between CONVERT(datetime,'20150506',112) and CONVERT(datetime,'20150510',112);

Sample SQL FIDDLE

Upvotes: 1

Related Questions