Arithmetic overflow error converting expression to data type datetime.

Question

with main as (
SELECT * FROM (
SELECT a.LBrCode,a.PrdAcctId,a.LongName,a.CustNo,a.AcctStat,b.ModuleType,b.PrdCd,a.DateClosed,
case when (a.AcctStat=3 and a.DateClosed >'31-MAR-2021') or a.AcctStat<>3 then 'live' else 'cl' end r1
FROM D009022 a INNER JOIN D009021 b ON a.LBrCode=b.LBrCode AND trim(substring(a.PrdAcctId,1,8))=trim(b.PrdCd)
AND b.ModuleType IN (13,14,30,31)
)  x WHERE r1='live'
)
,
limit as (
select * from (
select LBrCode brcodeh,PrdAcctId acnoh,ExpDate expdt,rank() over (partition by LBrCode,PrdAcctId order by EffFromDate desc) rnkx
from D009042 where EffFromDate<='31-mar-2020'
) x where rnkx=1
)
,
limit1 as (
select * from (
select a.*,rank() over (partition by a.LBrCode,a.PrdAcctId order by a.DisbursedDate desc) rnkm
from D009142 a where a.DisbursedDate<='31-mar-2021'
) x where rnkm=1
)
,
offset as (
  SELECT LBrCode,PrdAcctId , OffSet
            FROM
            (SELECT D001118.*,
            RANK() OVER(PARTITION BY LBrCode,PrdAcctId  ORDER BY EffFromDate DESC) AS RNK
            FROM D001118
            WHERE  EffFromDate<='31-mar-2021' and SrNo=1
) x
            WHERE RNK=1

)
,
int_rate as (
select * from (
SELECT  PrdCd,IntEffDt,IntRate,SrNo,
Isnull(lag(ToAmt,1) over (partition by PrdCd order by ToAmt)+1,0)  t1,
ToAmt from (
select a.*, RANK() OVER(PARTITION BY PrdCd ORDER BY IntEffDt DESC) AS rnk
from D001116 a where IntEffDt<='31-mar-2021'
) x where rnk=1
) y
)
,
bal as (
select LBrCode,PrdAcctId,
case when ModuleType in (30,31) then Balance1 else Balance4 end prn_ost,
case when ModuleType in (30,31) then (Balance2-Balance3 )  else 0 end int_ost,
case when ModuleType in (30,31) then  (Balance4-Balance5 )  else 0 end chrgs_ost,
case when ModuleType in (30,31) then  (Balance6-Balance7 )  else (Balance11-Balance12) end unapplied_int
from (
select b.ModuleType,a.*,RANK() OVER(PARTITION BY a.LBrCode,a.PrdAcctId ORDER BY a.CblDate DESC) AS rnk
from D010014 a inner join D009021 b on
a.LBrCode=b.LBrCode and rtrim(substring(a.PrdAcctId,1,8))=rtrim(b.PrdCd)
and b.ModuleType in (13,14,30,31)
where a.CblDate<='31-mar-2021') x where rnk=1 and floor(substring(PrdAcctId,17,8))<>0

)
--
select 
--a.LBrCode,a.PrdAcctId,a.LongName,a.CustNo,
b.expdt,
case when a.ModuleType in (30,31) then c.InstlStartDate else 0 end InstlStartDate,
d.OffSet+e.IntRate,
f.prn_ost,f.int_ost,f.chrgs_ost,f.unapplied_int
from main a inner join limit b on a.LBrCode=b.brcodeh and a.PrdAcctId=b.acnoh
inner join limit1 c on a.LBrCode=c.LBrCode and a.PrdAcctId=c.PrdAcctId
inner join bal f on a.LBrCode=f.LBrCode and a.PrdAcctId=f.PrdAcctId
inner join offset d on a.LBrCode=d.LBrCode and a.PrdAcctId=d.PrdAcctId
inner join  int_rate e on a.PrdCd=e.PrdCd and e.IntEffDt between t1 and ToAmt

Answer 1

hi guys
the above query giving me a captioned error
i am all blocked

plese help

Answer 2

ISSUE IS RESOLVED

thanks a lot
i was trying to compare effdt with amount
so was the error

thanks again