greater than and less than equals to in sql?

Question

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

Solution 1

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.

Solution 2

Try this

SELECT Date FROM TableName WHERE Date > @fromDate AND Date < @toDate

Solution 3

SELECT *
FROM dbo.Account AS a
WHERE 1 = 1
    AND (
        @toDate IS NULL
        OR a.CreateDate <= @toDate
    )
    AND (
        @fromDate IS NULL
        OR a.CreateDate >= @fromDate
    )

Please note the 1 = 1 is only there to make the conditions clear and is by no means needed.