The
BETWEEN[
^] operator is equivalent to:
SomeDateColumn >= @frdate AND SomeDateColumn <= @todate
Therefore, it's obvious that the upper-bound (
@todate
) must
ALWAYS be greater than or equal to the lower-bound (
@frdate
).
If the upper-bound is less than the lower-bound, then there is no value for which the condition be true.
For hard-coded bounds, it's simple enough to put them in the correct order. If the bounds are supplied by the user, and your UI is not capable of validating that the upper-bound is greater than or equal to the lower-bound, then you'll need to swap the values in SQL:
declare @frdate datetime, @todate datetime;
select @frdate='2016-04-01', @todate ='2015-12-01';
If @frdate > @todate
BEGIN
DECLARE @temp datetime;
SELECT @temp = @frdate, @frdate = @todate, @todate = @temp;
END;
select billdate from test where SomeDateColumn between @frdate and @todate order by billdate asc;