Forums - C# Corner

Forum guidelines
amit shukla

amit shukla

  • 1.6k
  • 78
  • 6.6k

Sql query slow when we cast date column as datetime.

Jul 17 2017 9:20 AM
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, from currency c inner join currency_master cm on as currencytbl
on at.currencyid=
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. 

Answers (8)


Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now