Arithmetic overflow error converting expression to

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Arithmetic overflow error converting expression to

Author

Topic

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2008-08-12 : 05:12:25

I have a SELECT statement that keeps returning the error "Arithmetic overflow error converting expression to data type smalldatetime. (.Net SqlClient Data Provider)". My statement is:

SELECT
MAIN.Reference,
MAIN.HSBC,
MAIN.STATUS,
MAIN.CANCEL,
MAIN.MODTERM,
MAIN.FILE,

MAIN.HEADING,
INF.Type_ID AS Type_ID,
INF.START,
SCD.LASTTIME AS LASTTIME,

CONVERT(VARCHAR(10), SCD.LASTTIME, 103) AS LastRunDate,
INF.AGENTCOUNT,

CASE MAIN.CANCEL WHEN 1 THEN 'CANCEL' ELSE CONVERT(VARCHAR(8), DATEDIFF(d, INF.START, SCD.LASTTIME))

END AS Formula
FROM
MID_RECORD AS MAIN INNER JOIN

New_File AS INF ON MAIN.FILE = INF.FILE INNER JOIN

MID_HEADER AS SCD ON MAIN.HEADING = SCD.HEADING

WHERE (CONVERT(SMALLDATETIME,SCD.LASTTIME) = @LastTime)

ORDER BY MAIN.Reference

Can anyone spot where the issue is? and mostly how to resolve it?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 05:17:14

SCD.LASTTIME is either before January 1, 1900 or after the year 2076.

edit
WHERE (CONVERT(SMALLDATETIME,SCD.LASTTIME) = @LastTime)

to
WHERE SCD.LASTTIME = @LastTime

E 12°55'05.25"
N 56°04'39.16"

jamie_pattison
Yak Posting Veteran

65 Posts

Posted - 2008-08-12 : 05:31:23

Thanks i tried that and it still comes up with an error:

Arithmetic overflow error converting expression to data type datetime. (.Net SqlClient Data Provider)

Error Number: 8115
Severity: 16
State: 2
Line Number: 1

The parameter value i enter is 16/05/2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 05:38:13

There is no such thing as month 16!

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-12 : 05:39:32

Never ever concatenate text and send to DB!
See this blog post http://weblogs.sqlteam.com/jeffs/archive/2008/07/24/60657.aspx

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts