Transact SQL assign to 'bit'

[agibsonsw]

Hello again. SQL Server 2005.

I've declared a variable @TestEquality as datatype bit, and would like to assign a boolean expression to it,
but the following gives me an error (near equals?!).

SET @TestEquality = (DATEDIFF(DAY,@SomeDate,'2010-08-25')=0);

Any idea what's wrong with this expression please?

@SomeDate is a variable of type date and has been assigned the value GETDATE().

Thanks, Andy.

[HansV]

Does this work?

SET @TestEquality = cast((DATEDIFF(DAY,@SomeDate,'2010-08-25')=0) as bit);

or even

SET @TestEquality = cast((case when (DATEDIFF(DAY,@SomeDate,'2010-08-25')=0) then 1 else 0 end) as bit);

[Wendell]

The DATEDIFF() function returns an Integer value based on the values of StartDate and EndDate, so in your case today it would return a 0. I think the problem is with the "=0" bit at the end of your expression. You are essentially trying to set 0=0. However as Hans suggests, you probably need to do some sort of cast function as well, and you need to deal with the situation where the result is not 0 or 1.

[agibsonsw]

Thanks both. I got it working using CASE. Andy.