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

Question

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

just note, why two times file_name in select list in query — HaveNoDisplayName
– HaveNoDisplayName, Commented May 13, 2015 at 17:47
Piyush : taking substring of file name , so used file_name twice. — user3863488
– user3863488, Commented May 13, 2015 at 17:52
Lashane : I tried replacing datetime with int, still it is showing exception — user3863488
– user3863488, Commented May 13, 2015 at 17:52
@user3863488:- I not talking about sub-string, I mean to pointed out this code select file_name,file_name in your query — HaveNoDisplayName
– HaveNoDisplayName, Commented May 13, 2015 at 17:56

HaveNoDisplayName · Accepted Answer · 2015-05-13 19:08:08Z

1

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

edited May 13, 2015 at 19:08

answered May 13, 2015 at 18:06

HaveNoDisplayName

8,537106 gold badges41 silver badges50 bronze badges

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

Comments

Pang · Accepted Answer · 2015-08-17 09:31:39Z

0

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);

edited Aug 17, 2015 at 9:31

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered May 14, 2015 at 7:55

user3863488

2271 silver badge16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related