T-SQL Between Statement

Question

Post reply

T-SQL Between Statement

JeffE

Mr or Mrs. 500

Points: 527
More actions
June 3, 2003 at 3:49 pm

#80998

I have discovered a broken IF statement in a stored procedure that utilizies the between statement. The statement looks as follows: "If @CurrentDate between @MaxDate and @MinDate"

If my current date was between these dates it failed. When I reversed the @MaxDate with the @MinDate it worked. This stored procedure has been working for years until recently. Has this just been working by sure luck or has there been a fix for this in a previous patch? The last patch I applied was SQL 2000 sp3. Any reference documentation would be great. Thanks.

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

I would say luck, however could have been an issue.

With BETWEEN the smallest value should be first and the largest after the AND. They don't express that in the documentation except under remarks in BOL topic "BETWEEN"

quote:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

mccork SSC-Insane Points: 22288 More actions · Answer 2

I agree you've probably been lucky until now. Any chance that your 3 variables have all contained the same date up until recently?

Fix list for SQL2000 SP3 has no reference to changes affecting BETWEEN behaviour.

I tried the following SQL on several flavours of SQL2000 (from RTM through SP3) and SQL7.0 (SP3 and SP4) and result was a consistent "no"....

select case when 1 between 2 and 0 then 'yes' else 'no' end

Cheers,
- Mark

JeffE Mr or Mrs. 500 Points: 527 More actions · Answer 3

Thanks. I also did some more testing and found that I could not get the statement to work as it was. I was in need of some proof that it had never worked and all I could prove was that it did not work in my current environment. Just think, this has been in production since 97 and it just now bit us. Once again thanks.