CODEWITHSUNDEEP
c#sqlentity-frameworkdatetime
user2740190
user2740190

Reputation:

Searching on Date Fields when some date rows are NULL on the back-end

User Picks some from and to dates and with SqlBuilder I am creating the query string to be run the database records. So part of it that generated query may look like this:

WHERE  ( t1.From_Date >= @from_date AND t1.To_Date <= @to_date )

BUT for some reason that I am not aware of, if I go to SQL Server and see the rows some of them have NULL in their from or to columns. So it causes my query to not return those rows. Is there a way I can fix this in C# side of the code?

Upvotes: 0

Views: 49

Answers (3)

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171451

I would fix the query to do something like this:

where (isnull(t1.From_Date, @from_date) >= @from_date 
    and isnull(t1.To_Date, @to_date) <= @to_date)

Variation:

where (t1.From_Date is null or t1.From_Date >= @from_date) 
    and (t1.To_Date is null or t1.To_Date <= @to_date)

Upvotes: 0

foxtrotZulu
foxtrotZulu

Reputation: 1119

It would be best if you fix your data first, otherwise just do a 'select *' and as you rifle through the records, instantiate a class that loads each property, if prop is null make it whatever value..save class object to hashtable and use set of records as you like.. KEEP IN Mind, this is only if you have a need to filter or present the data in a certain way..Use queries below if you just want to omit the records..

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

If you WANT to include those rows just adjust your WHERE caluse:

WHERE  t1.From_Date IS NULL OR
       t1.To_Date   IS NULL OR
       ( t1.From_Date >= @from_date AND t1.To_Date <= @to_date )

Upvotes: 1

Related Questions