Querying on null datetime

www Developerof NET

Hi, I have a table which has a date column "OrderedDate" which can be null in db. Secondly i have a UI which has an frmdate and todate. While querying if the two textboxes are empty i need to show all the records( including the rows with null date). I have written the following query but it fetches the ros with non null values only.

AND
(
isnull(OrderedDate,@frmdate) between isnull(@frmdate,OrderedDate) and isnull(@todate,OrderedDate)
)

I can`t figure out whats the problem. Kindly look into the matter if possible. Thank You.

When you fail to plan, you are planning to fail.

loyal ginger

You can construct separate queries for cases when the text boxes are empty.

Luc Pattyn

Your logic seems flawed; I would try

AND
(
@frmdate IS NULL OR @todate IS NULL OR OrderedDate BETWEEN @frmdate AND @todate
)

warning: BETWEEN is inclusive or exclusive, depending on the actual database; when uncertain use explicit comparison with >= and <= :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

Luc Pattyn

While I already answered your actual question, what I would really do myself is:

AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)

That allows you to leave either one empty, so you can get: - all - all up to some date - all since some date - all in an interval :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

Lost User

There are two things you can do in this case. 1. Either you can check the null value of @frmdate and @todate at programming level and then fire queries accordingly. 2. You can write the same condition in Stored procedure and fire query conditionally. But, by using single query it is not possible. Because when the @frmdate and @todate is null it will always consider those fields with value and ignore rows having null in OrderDate column, there are reason for such behavior. 1. Either between is evaluating while there is value in OrderDate column and ignoring row while it is null. Hope I will clear your problem.

Jinal Desai

PIEBALDconsult

I would take another approach. The Business Layer should not allow the UI layer to pass null values in; it should require proper DateTime values. The UI layer should then pass in something like System.Data.SqlTypes.SqlDateTime.MinValue or System.Data.SqlTypes.SqlDateTime.MaxValue as appropriate. You are parsing the TextBox values to DateTimes before passing them to the Business Layer aren't you? In fact, why aren't you using DateTimePickers? :confused:

Lost User

Following is conditional code that will work for your situation. declare @date1 as DateTime; declare @date2 as DateTime; If both the dates are specified the query goes as follow set @date1='1970-04-04 00:00:00.000'; set @date2='1978-07-07 00:00:00.000'; if @date1 is null or @date2 is null begin select * from tblemployee end else begin select * from tblemployee WHERE birthdate between @date1 and @date2 end If any of the field or both of the field is null then the query goes as follow set @date1=NULL; set @date2='1978-07-07 00:00:00.000'; if @date1 is null or @date2 is null begin select * from tblemployee end else begin select * from tblemployee WHERE birthdate between @date1 and @date2 end

Jinal Desai

www Developerof NET

PIEBALDconsult wrote:

In fact, why aren't you using DateTimePickers?

Actually i am. I have a layered architecture and pass the datetime values as Nullable which allows me to send a null datetime to my SP. Anyways, Luc`s answer fits correctly for me.

AND
(
@frmdate IS NULL OR OrderedDate >= @frmdate
}
AND
(
@todate IS NULL OR OrderedDate <= @todate
)

Thanks again.

When you fail to plan, you are planning to fail.