how can i use greater than equal to and less than equal to instead of using between in SQL
I'm checking for date variable in sql
i tried like this.
Date between coalesce(@fromDate,Date) and coalesce(@toDate,Date)
but if user does not enter any of date (fromDate or toDate)
so that I need to convert above condition in greater than equal to and less than equal to
please help in syntax in sql. thanks
IF @fromDate IS NULL
BEGIN
SET @fromDate = '1900-01-01';
END;
IF @toDate IS NULL
BEGIN
SET @toDate = '2099-01-01';
END;
SELECT things
FROM table
WHERE date_field BETWEEN @toDate AND @fromDate;
This code will essentially give you an arbitrarily large range to check which should give you reasonable performance and return all results (assuming that's what you want when neither value is supplied).
This code can be shortened to:
SELECT things
FROM table
WHERE date_field BETWEEN Coalesce(@toDate, '1900-01-01') AND Coalesce(@fromDate, '2099-01-01');
But I kept the verbose version to illustrate.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments