overflow in query - SQL Server Forums

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)

overflow in query

Author

Topic

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-11-09 : 14:46:42

folks I have a column which is Numeric 18, which is basically a date type

I run the following query against it
select dateadd(dd,floor(dates.Column0/1000000/86400),'01/01/1960 00:00:00')
from dbo.dates

I get this error
Arithmetic overflow error converting expression to data type int.

Regards
Paresh Motiwala
Boston, USA

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-11-09 : 15:17:12

The Values typically are 1620556868093680, 1636182788871210 and so on
About 2 million records.

Regards
Paresh Motiwala
Boston, USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 00:40:27

sorry your expression doesnt make sense. you're trying to add days ? what does column0 contain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2011-11-10 : 09:09:22

it was a date of some format, but I figured out the issue.
The number in the Dateadd field has to be INT, this one is "Numeric"; that was one gotcha.
The second gotcha was there was a '-' in one of the numbers....
Thanks for your response.

Regards
Paresh Motiwala
Boston, USA

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-10 : 09:58:15

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources