declare @cur cursor
declare @date smalldatetime
declare @rate real

set @cur = cursor

for select distinct LEFT(CONVERT(varchar, dOrdered, 101), 10) as dOrdered from Invoice 
	where dordered is not null
open @cur
fetch next from @cur into @date
while @@fetch_status = 0
begin
        -- using top 1 to get the nearest exchange rate if there wasn't an entry on that date
	select TOP 1 ddate, rCADExchange from tblCADExchange
		where left(convert(varchar, dDate, 101), 10) <= @date
		order by ddate desc

	
	update invoice set rCADExchange = @rate
	where LEFT(CONVERT(varchar, dOrdered, 101), 10) = @date
	and rCADExchange is null

	fetch next from @cur into @date
end
close @cur
deallocate @cur
go

UPDATE Invoice
SET Invoice.rCADExchange  = exch.rCADExchange 
FROM 
	tblCADExchange as exch 
WHERE 
	CONVERT(varchar, exch.dDate,112) = 
	(SELECT MAX(dDate) FROM tblCADExchange AS a 
         HAVING MAX(CONVERT(varchar, a.dDate, 112))<=CONVERT(varchar, Invoice.dOrdered, 112))