Problem with DATEDIFF

I currently have a problem with DATEDIFF(). When I run the below code, it works the way I expect it should. However, running this logic in a trigger produces a different result. I can consistenly reproduce this and have not figured out the cause.

DECLARE

@.Hours INT,

@.LastPlayDate DATETIME,

@.CardOutTime DATETIME

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

SET @.hOURS =DATEDIFF(hh, @.LastPlayDate, @.CardOutTime)

ifDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

BEGIN

print'greater'

SELECT @.HOURS

END

else

BEGIN

print'lessor'

SELECT @.HOURS

END

Trigger Code

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12--@.TripLength

-- @.Hours > @.TripLength

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

I know that the last play date is less than the card out time and that the hours between these is greater than 12 in this csae and this shows correctly in the results pane. When this logic is implemented in a trigger, instead of the results being "greater", I get "lesser". When I set the results to another variable so I can see what the value is, it always returns a negative number. Any insight into this is appreaciated.

Hi MAGrimsley,

I can not reproduce the problem. I am using SS2005 DE SP2.

createtable dbo.t1 (

c1 int

)

go

createtrigger tr_t1_ins on dbo.t1

forinsert

as

DECLARE @.Hours INT, @.LastPlayDate DATETIME, @.CardOutTime DATETIME

declare @.NewTrip int

set @.LastPlayDate ='1900-01-01 00:00:00.000'

set @.CardOutTime ='2005-04-22 18:33:51.000'

IFDATEDIFF(hh, @.LastPlayDate, @.CardOutTime)> 12

SET @.NewTrip = 1;

ELSE

SET @.NewTrip = 0;

select @.NewTrip

go

insertinto dbo.t1 defaultvalues

go

droptable dbo.t1

go

Result:

1

AMB

|||

AMB

That is the mystifying part. I copy the code into another window and it runs the way I expect it to. The fact it is in a trigger should not make a difference, but the result I get is. I have even rewritten the trigger, but the result is the same.

MAGrimsley

|||Version--SQL Server 2005 Enterprise (IA64) 9.00.3054.00