Datetime - Selects, Variables and confusion

Question

Post reply

Datetime - Selects, Variables and confusion

Dean Besley

Newbie

Points: 8
More actions
May 19, 2006 at 6:32 am

#168204

G'day folks,
Would appreciate a little light shed on this one...

Following is a test stub in SQL 2005.

--------------------
DECLARE
@Triggered_Datetime [datetime],
@Triggered_Price [money]

SET @Triggered_Datetime = '2006-03-01 01:03:59.000'
SET @Triggered_Price = 1.2017

SELECT TOP (1) Ticks.Tick_Identity, ticks.Tick_DateTime
FROM Ticks
WHERE Ticks.Tick_Price = @Triggered_Price AND
Ticks.Tick_DateTime BETWEEN @Triggered_Datetime AND
Dateadd(Hour,2,@Triggered_Datetime)
ORDER BY Ticks.Tick_Identity
-------------------------
(Note: Ticks.Tick_Price is a datetime)

Now this hangs (cancelled query after 5 mins).

If however, I replace the @Triggered_Datetime in the WHERE clause with the actual datetime value (as below), it works fine (2 second response).

------------------------------
DECLARE
@Triggered_Datetime [datetime],
@Triggered_Price [money]

SET @Triggered_Datetime = '2006-03-01 01:03:59.000'
SET @Triggered_Price = 1.2017

SELECT TOP (1) Ticks.Tick_Identity, ticks.Tick_DateTime
FROM Ticks
WHERE Ticks.Tick_Price = @Triggered_Price AND
Ticks.Tick_DateTime BETWEEN '2006-03-01 01:03:59.000' AND
Dateadd(Hour,2,'2006-03-01 01:03:59.000')
ORDER BY Ticks.Tick_Identity
----------------------------

Any advise/thoughts would be great, thanks
DeanB

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SQLZ SSChampion Points: 12872 More actions · Answer 1

Dean,

tough to say what the problem is here.

Try setting the value of a second date parameter to dateadd(Hour, 2....) beforehand. As in the following example. See if that makes a difference.

How big is this table by the way?

DECLARE

@Triggered_Datetime [datetime],

@Triggered_Price [money],

@TwoHour_Datetime [datetime]

SET @Triggered_Datetime = '2006-03-01 01:03:59.000'

SET @Triggered_Price = 1.2017

SET @TwoHour_Datetime = Dateadd(Hour,2,@Triggered_Datetime)

SELECT TOP (1) Ticks.Tick_Identity, ticks.Tick_DateTime

FROM Ticks

WHERE Ticks.Tick_Price = @Triggered_Price AND

Ticks.Tick_DateTime BETWEEN @Triggered_Datetime AND

@TwoHour_Datetime

ORDER BY Ticks.Tick_Identity

Karl
source control for SQL Server

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

i've seen this behaviour on betweens before.

try using where Ticks.Tick_Price = @Triggered_Price AND

Ticks.Tick_DateTime >= @Triggered_Datetime AND

Ticks.Tick_DateTime<=(dateadd......

no idea why it fixed the problem but it did - possibley something to do with the arguments being sargable

MVDBA

Dean Besley Newbie Points: 8 More actions · Answer 3

Thanks for your reply’s Mike and Karl,

Unfortunately neither suggestions make any difference.

It is a big file, and TOP (1) & ORDER BY have their own nuances, but its confusing that the code works when I use actual values other than the variables.

My feeling is that it is a CAST issue, but the datatype in the table is definitely datetime.

Back to the drawing board!

Regs

DeanB