Sql query slow when we cast date column as datetime.

Question

Hi Friends,

 

Today i am facing some problem regarding sql query define below.

 

select at.*,currencytbl.currency,am.account_name,(case when at.isauthorized is null then 'not approved' else 'approved' end) as [status]

,(um.First_Name + ' ' + um.Last_Name) as username from account_transaction AT inner join

(select c.currency,c.id from currency c inner join currency_master cm on c.id=cm.currency_shortname) as currencytbl

on at.currencyid= currencytbl.id

inner join account_master AM on AM.account_id=at.accountid inner join USERS_DETAIL ud on am.Account_Code = ud.UserFCStockAccNo

inner join USERS_MASTER um on ud.User_Id = um.User_Id

where AT.dno in(select dno from account_transaction where transtypeid=11and creditdebit='D')

and AT.creditdebit='C' and AT.currencyid != 62

and (CAST(CreatedOn AS DATE) >= CAST('07/17/2017' AS DATE) AND CAST(CreatedOn AS DATE) <= CAST('07/17/2017' AS DATE))

 

this query work fine when we does not take any date field column then it executes fine with in 1 sec but when we add date column

with casting then it takes very long time to execute. 

 

Plz Resolve.