Microsoft SQL Server, Error: 8115

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 2008 Forums

Transact-SQL (2008)

Microsoft SQL Server, Error: 8115

Author

Topic

sonjan
Starting Member

22 Posts

Posted - 2011-11-07 : 18:33:44

I am receiving the above error: Arithmetic overflow error converting expression to data type datetime. (Microsoft SQL Server, Error: 8115).

I have converted a date field and want to use variables in my select clause - replacedate between @StartDate and @EndDate. This query works fine in SQL Server but when copied to a SQL Report and inserting report parameters, the above error occurs when executed from the 'Data' tab in SQL Report. When I try to preview the report the following error appears: 'The property 'DefaultValue' of report parameter 'EndDate' doesn't have the expected type'. My original query is as follows:

select
assets.assetnum,
assets.description,
people.displayname,
assets.plusteqtype,
assets.replacecost,
convert(nvarchar,assets.installdate, 103) as AcqDate,
assets.replaceyears,
convert(nvarchar,assets.replacedate, 103) as ReplaceDate,
fuel.lastodometer
from
(select assetnum, description, assignedto, plusteqtype, replacecost, installdate, replaceyears, replacedate
from asset with (nolock)
where orgid = 'rsc' and siteid = 'fleet' and status = 'operating' and plusteqtype in ('DUALCAB','EXTRACAB','SINGLECAB')
) assets
inner join
(select assetnum, max(odometer) lastodometer
from fueltrans with (nolock)
where orgid = 'rsc' and siteid = 'fleet'
group by assetnum
) fuel
on
fuel.assetnum = assets.assetnum
left join
(select personid, displayname
from person with (nolock)
) people
on people.personid = assets.assignedto
where
assets.replacedate between @StartDate and @EndDate

Any help would be much appreciated.

Thank you.

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-11-07 : 22:52:31

Please provide the create table structure
asset
fueltrans
person

and few dummy insert statement.

Will try to figure out in SQL editor once received above information.

Thank you

sonjan
Starting Member

22 Posts

Posted - 2011-11-08 : 00:04:26

Just a new comer to SQL. Not trying to create tables or use insert statements - just a plain select statement with joins.
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-08 : 00:09:38

seems like problem is with default value you're setting for enddate. make sure its a valid datetime value

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

micnie_2020
Posting Yak Master

232 Posts

Posted - 2011-11-08 : 01:55:55

Hi,

Here is a working example.

create table asset
(
assetnum int identity(1,1),
[description] varchar(100) null,
assignedto int null,
plusteqtype varchar(100) null,
replacecost varchar(100) null,
installdate datetime null,
replaceyears varchar(4) null,
replacedate datetime null,
siteid varchar(20),
orgid varchar(20),
[status] varchar(10),
)

create table fueltrans
(
assetnum int identity(1,1),
odometer varchar(100) null,
orgid varchar(20),
siteid varchar(20)
)

create table person
(
personid int identity(1,1),
displayname varchar(200)
)

insert into asset
([description],assignedto,plusteqtype,replacecost,installdate,replaceyears,replacedate,siteid,orgid,[status])
values
('TestA',1,'DUALCAB','300','01/Jan/2009','2012','02/Jan/2012','fleet','rsc','operating')

insert into fueltrans
(odometer,orgid,siteid)
values
('100','rsc','fleet')

insert into person
(displayname)
values
('John')

------------------------------ Run Below code after you create above table for testing ----------------

declare @StartDate datetime
declare @EndDate datetime

set @StartDate='01/Jan/2009'
--set @EndDate='01/Jan/2011'
set @EndDate='01/Feb/2012'

select
assets.assetnum,
assets.description,
people.displayname,
assets.plusteqtype,
assets.replacecost,
convert(nvarchar,assets.installdate, 103) as AcqDate,
assets.replaceyears,
convert(nvarchar,assets.replacedate, 103) as ReplaceDate,
fuel.lastodometer
from
(select assetnum, description, assignedto, plusteqtype, replacecost, installdate, replaceyears, replacedate
from asset with (nolock)
where orgid = 'rsc' and siteid = 'fleet' and [status] = 'operating' and plusteqtype in ('DUALCAB','EXTRACAB','SINGLECAB')
) assets
inner join
(select assetnum, max(odometer) lastodometer
from fueltrans with (nolock)
where orgid = 'rsc' and siteid = 'fleet'
group by assetnum
) fuel
on
fuel.assetnum = assets.assetnum
left join
(select personid, displayname
from person with (nolock)
) people
on people.personid = assets.assignedto
where
assets.replacedate between @StartDate and @EndDate

Thank you.

Regards,
Micnie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources