"Arithmetic overflow error converting expression to data type datetime" SQL SERVER

Question

I am currently getting this error and i am completely lost as to what the issue is. I tracked the error down to a view and completed a select * statement from it and i get that message. Now, the weird thing is is that this error has never appeared on this view before. It has always ran without any issues and nothings been changed so i am unsure as to what the issue is.

I cant show the data for security reasons but it holds 2 date fields that are

 datetime, not null

but i am not trying to change this data in anyway, i am simply just trying to select it.

The only other fields i thought it might concern are

decimal (38,20)

could this be the issue?

Sorry for the lack of information regarding the tables but i have literally tried everything from casting the fields to larger decimals, floats etc and still nothing.

It would be great if there was any small advice anyone could give that would possibly point me in the right direction?

UPDATE

If i do a

select top (1000)

query it works but any larger and i get that error

is there any data type casting or conversion inside the view? — Abdul Rasheed
– Abdul Rasheed, Commented Nov 16, 2017 at 10:04
Could you post what the view looks like? I’m betting your datetime field is joining to a non-datetime field. — calamities
– calamities, Commented Nov 16, 2017 at 10:04
@AbdulRasheed did you see my update? why would it work with a 1000 limit but not work for everything — Ryan Gadsdon
– Ryan Gadsdon, Commented Nov 16, 2017 at 10:06
You should update your question with a view text. You can substitute table names with T1...Tn if you don't want to show your real table names. There is some join of type varchar = datetime or smth like this — sepupic
– sepupic, Commented Nov 16, 2017 at 10:11

Niranjan Rajawat · Accepted Answer · 2017-11-16 10:11:26Z

2

If using sql server version 2012 or higher, try use TRY_CONVERT function. It goes like

SELECT TRY_CONVERT(datetime, ColumnName).

NOTE: It will return a NULL if ColumnName contains data which can't be converted to a datetime.

answered Nov 16, 2017 at 10:11

Niranjan Rajawat

5632 silver badges9 bronze badges

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

Comments

Yogesh Sharma · Accepted Answer · 2017-11-16 10:12:31Z

0

i think there are some wrong date has been inserted in database, you need to use

ISDATE() sql function for same eg. like below in your select query

 (CASE WHEN ISDATE (your_date) = 1 
             THEN convert(datetime, cast([your_date] as char(8)))
             END) AS your_Date

edited Nov 16, 2017 at 10:12

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

answered Nov 16, 2017 at 10:02

NAVEEN BISHT

116 bronze badges

Collectives™ on Stack Overflow

"Arithmetic overflow error converting expression to data type datetime" SQL SERVER

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