Urgent!!!My Sql Query getting Arithmetic overflow error converting expression to data type datetime.

Posted by Ravianand under Sql Server on 7/5/2012 | Points: 10 | Views : 2784 | Status : [Member] | Replies : 3
My Sql Query getting Arithmetic overflow error converting expression to data type datetime.
How to overcome from that error

MY Query is,

select a.Entity,a.Vehicle,a.VehicleNo as [VehicleNo.],a.Department,b.DayShift as [Day Shift(in Hrs)],
b.NightShift as [Night Shift(in Hrs)],b.TotalHours as [Total Hours(in Hrs)],a.Diesel as [Diesel consumed],
a.Cost,a.Remarks from (SELECT dm.intentityid,e.nvrentityname as[Entity], vt.nvrvehicletype as [Vehicle],
dm.nvrvehicleno as [VehicleNo], d.nvrdepartmentname as[Department], Sum(dm.fltdieselfillqty) as [Diesel],
(sum(dm.fltdieselfillqty)*(select fltcost from tblSEZ_AD_DieselRate
where intdiselid=(select max(intdiselid) from tblSEZ_AD_DieselRate))) as Cost,
dm.nvrremarks as [Remarks] FROM dbo.tblSEZ_FT_MonthlyVehicleUtilization as dm
left join tblSEZ_AD_VehicleType as vt on vt.intvehicleid=dm.intvehicleid
left join dbo.tblSEZ_AD_Entity as e on e.intentityid=dm.intentityid
left join dbo.tblSEZ_AD_Department as d on d.intdeptid=dm.intdeptid
where month(dm.dttdate)=7 AND Year (dm.dttdate)=2012 And e.nvrentityname='NCTPL Owned P&M Vehicle'
group By dm.intentityid,e.nvrentityname,vt.nvrvehicletype, dm.nvrvehicleno,d.nvrdepartmentname, dm.nvrremarks ) a
inner join (SELECT t.intentityid,STUFF(CONVERT(CHAR(5), DATEADD(SECOND, dayshift, '19000101'), 8), 1, 2,
CAST(dayshift / 3600 AS VARCHAR(12))) as [DayShift], STUFF(CONVERT(CHAR(5),
DATEADD(SECOND, nightshifts, '19000101'), 8), 1, 2,CAST(nightshifts / 3600 AS VARCHAR(12))) as [NightShift],
STUFF(CONVERT(CHAR(5), DATEADD(SECOND, TotalHr, '19000101'), 8), 1, 2,CAST(TotalHr / 3600 AS VARCHAR(12))) as [TotalHours]
FROM( SELECT intentityid,ABS(SUM(DATEDIFF(SECOND, '00:00', nvrdayshift))) AS dayshift, ABS(SUM(DATEDIFF(SECOND,
'00:00', nvrnightshift))) AS nightshifts, ABS(SUM(DATEDIFF(SECOND, '00:00', nvrtotalhr))) AS
TotalHr FROM tblSEZ_FT_MonthlyVehicleUtilization where month(dttdate)=7 AND Year (dttdate)=2012
And intentityid=03 group by intentityid) AS t ) b on a.intentityid=b.intentityid


Error is

Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

Regards,
Ravi



Responses

Posted by: Ravianand on: 7/5/2012 [Member] Starter | Points: 25

Up
0
Down
My Scenario is getting monthly report
Field name is dayshift, nightshift (time is nvarchar format) input is 12:45 and 22:23 want to add both time




Regards,
Ravi

Ravianand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muralidosscm on: 7/6/2012 [Member] Starter | Points: 25

Up
0
Down
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=115982

Regards
Muralidoss M

Ravianand, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/6/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

I think you are getting this error because your field size is less than your data .
So check you table Design and data type with query

Regards

R D Patel

Ravianand, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response