Solve an Arithmetic overflow error in sql?

Question

I am trying to run the following query but keeps returning an error.

(select distinct a.no_,a.rowno,a.rowno_glglhi_gl,a.amount as amount,a.effective ,a.branch from
(
--gls from the last 40 days 
select ks208.dbo.gl.no_,ks208.dbo.glhi.rowno,ks208.dbo.glhi.rowno_glglhi_gl,ks208.dbo.glhi.amount,ks208.dbo.glhi.effective,ks208.dbo.gl.branch
from ks208.dbo.glhi
left join ks208.dbo.gl on ks208.dbo.gl.rowno=ks208.dbo.glhi.rowno_glglhi_gl

union 
--gls from the larger database
select dmon208.dbo.gl.no_,dmon208.dbo.glhi.rowno,dmon208.dbo.glhi.rowno_glglhi_gl,dmon208.dbo.glhi.amount ,dmon208.dbo.glhi.effective,dmon208.dbo.gl.branch
from dmon208.dbo.glhi
left join DMON208.dbo.gl on DMON208.dbo.gl.rowno=dmon208.dbo.glhi.rowno_glglhi_gl) as a

union

--gls with no glhi
select extra.no_,extra.rowno,extra.rowno_glglhi_gl,extra.amount,extra.effective,extra.branch from
(select distinct gl.no_,gl.rowno,dateadd(year,-10,getdate()) as 'effective',0.00 as 'amount',gl.branch,gl.rowno as rowno_glglhi_gl from gl 
left join glhi on glhi.rowno_glglhi_gl = gl.rowno
where gl.status != 'closed'
and gl.no_ not in( select distinct gl.no_ from gl
inner join glhi on glhi.rowno_glglhi_gl = gl.rowno 
where gl.status != 'closed')

union 

select distinct gl.no_,gl.rowno,dateadd(day,-1,getdate()) as 'effective',0.00 as 'amount',gl.branch,gl.rowno as rowno_glglhi_gl from gl 
left join glhi on glhi.rowno_glglhi_gl = gl.rowno
where gl.status != 'closed'
and gl.no_ not in( select distinct gl.no_ from gl
inner join glhi on glhi.rowno_glglhi_gl = gl.rowno 
where gl.status != 'closed')) as extra

union 

select gl.no_,gl.rowno,dateadd(day,-1,getdate()) as 'effective',0.00 as 'amount',gl.branch,gl.rowno as rowno_glglhi_gl from gl 
left join glhi on glhi.rowno_glglhi_gl = gl.rowno
where glhi.rowno_glglhi_gl
in(
(-- gl's not updated yesterday
select distinct glhi.rowno_glglhi_gl from glhi where glhi.rowno_glglhi_gl
not in (
--gl's updated as of yesterday
select distinct glhi.rowno_glglhi_gl from glhi where
glhi.effective = cast(convert(char(11), dateadd(day,-1, getdate()), 113) as datetime))))


) as final 

where year(final.effective) >= year(getdate())-4
group by final.no_,final.effective,final.branch,final.rowno_glglhi_gl

order by final.no_,final.rowno_glglhi_gl,final.effective desc

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

The issue is with

left join glhi on glhi.rowno_glglhi_gl = gl.rowno
where glhi.rowno_glglhi_gl
in(
(
select distinct glhi.rowno_glglhi_gl from glhi where glhi.rowno_glglhi_gl
not in (
select distinct glhi.rowno_glglhi_gl from glhi where
glhi.effective = cast(convert(char(11), dateadd(day,-1, getdate()), 113) as datetime))))

For some reason it runs well on it's own but not as part of the larger query .

I have tried:

select cast(convert(char(11), dateadd(day,-1, getdate()), 113) as datetime)

select DATEADD(dd, DATEDIFF(dd, 1, getdate()), 0)

select CONVERT(DATE, dateadd(day,-1, getdate()), 101) 

Answer 1

Why are you converting a date/time to a string and then back to a date, just to remove the time component?

I suspect you want:

glhi.effective = convert(date, dateadd(day, -1, getdate()))

A comparison between date and datetime should be fine.