Strange issue in a query..... What can I do?

[RovirozM]

Hi guys,

This looks very simple but I don't know why this is happening...

I have a condition in my query like this:

Where (sm.TransactionDate >= @LastDayInWeek and sm.TransactionDate <= @LastDayInWeek)

When I run my query like that it tooks around 14 seconds to finish.

But if I do this:

Where (sm.TransactionDate >= '4-SEP-2010' and sm.TransactionDate <= '10-SEP-2010')

It tooks 1 Second to finish...

I tried converting my vars to different formats and always when I use variables it took 14 seconds...

Do you know why this happen? I need to use variables on my query....

Thanks for any suggestion!

MR

PD:
This is my original query (just in case) but the necessary information is what I mentioned before

Select 'SWS',
sc.DefectDescription,
Sum([Quantity] * [Std_Unit_Cost]) As CostoProdScrap
From TSc_ScrapTicketsMaster sm Inner Join TSc_ScrapTicketsDetail sd
On sm.ScrapTicket = sd.ScrapTicket
Inner Join @Lines li
On sm.LineNumber = li.LineNumber
Inner Join Maxcim..SwsHol_INCMS mx
On sd.PartNumber = mx.Part_Number
Inner Join TSc_ScrapCodes sc
On sd.DefectCode = sc.DefectCode
Where (sm.TransactionDate >= @LastDayInWeek and sm.TransactionDate <= @LastDayInWeek)
And sm.Transfered = 'P'
And sm.ScrapType In ('EMPAQUE','ENTRENAMIENTO', 'PROCESOS', 'PRODUCCION', 'REVISIONES ANTERIORES', 'SERVICIO')

 

[gmmastros]

What is the data type of sm.transactiondate and the data type of your variable? Make sure they are the same.

Also, in your hard coded date example, the dates were different, but it looks like you are using the same variable. I'm not saying it's wrong, but it's not what I would have expected.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom