Блог

 select t1.InvoiceNumber ,t1.LocalAmount  , t2.LineAmount as discount , t1.CreateDate
from CashOrderTrn t1 
left join DistrubutedDiscountDetails t2 
    on t1.InvoiceNumber =  t2.InvoiceNumber  
    and t1.CreateDate between '20160531' and  '20160701' 
    and t1.InvoiceType ='31'
  

Переместите условия t1 в WHERE предложение:

 select t1.InvoiceNumber ,t1.LocalAmount , t2.LineAmount as discount ,
      t1.CreateDate
from CashOrderTrn t1
left join DistrubutedDiscountDetails t2 on t1.InvoiceNumber = t2.InvoiceNumber
WHERE t1.CreateDate between '20160531' and '20160701' and t1.InvoiceType ='31'
  

Кстати, вы храните даты в символьном столбце? Я бы рекомендовал date вместо этого столбец.