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