Where statement with null and not null value

[Lethugs]

Hi,

How to use in select statement with where condition comparing a Null value and with value?

I have this Statement

Select Col1, Col2, Col3 From Table where DateDeleted 'Here comes the problem

The condition is something like if DateDeleted (which is actually a date) is Null then get records with null DateDeleted, else it should compare the DateDeleted to specified date. If this DateDeleted is less than (or earlier) than the specified date then it will show the records, else it bypass it.

The idea of the select statement is to get all data with null DateDeleted and those having DateDeleted but less than (or earlier) than a given specific date

I am using vb.net 2010 and Sql server 2008 r2.

Hope somebody can help me for im running of time trying to figure this out

thank you in advance

[Reverend Jim]

Try

SELECT * FROM Table
 WHERE DateDeleted < '2014-02-12'
    OR DateDeleted IS NULL

[Lethugs]

Hi Jim

I've tried that idea already, works fine but when DateDeleted is already greater than (later than) the specified date, the record is still included. I dont understand thinking that this condition should work. I also tried to convert this dates so that they will have the same format but still same result, is there any other way?

thanks again

[Reverend Jim]

I tested the query on a sample database and it worked just the way you want.

[rch1231]

Hello,

I would take a look at a couple of things.
First what type of field are you storing the date in? It should be a Date or Datetime field if it is just a text or varchar field then that is your issue.
Second, What is the format of the date that is stored in your field? Make your test against a field with the same format (YYYY/MM/DD hh:mm:ss).

If nothing else give us the definition of your table and a couple lines of output that includes the DateDeleted and at least one record with DateDeleted has data.

[Lethugs]

Hi,
The field is of type varchar with format mm/dd/yy, but in my query I am converting it to date. Im trying to figure out why solution of Jim does'nt work for me as expected.
Anyway here's an overview of my table

Tno(Pk)    TranNo      IID      User      DateAdded       DateDeleted
1           1           25      3           03/10/2014      NULL
2           2           18      4           03/15/2014      03/20/2014

so when I extract records which dateAdded < 03/19/2014, all records should be included since DateDeleted of TranNo 2 is > than the given date. Logically during that that TranNo is not yet deleted. Bu if the given date let say is 03/21/2014, TranNo 2 should not be included

thanks again

[urtrivedi]

SELECT * FROM Table
WHERE  (convert(varchar,DateDeleted,1)< '2014-02-12'
OR DateDeleted IS NULL)

You should always use proper datatime datatype when value is date,
Here you have to always convert varchar to date now

refer following for sql date conversions
http://sqlusa.com/bestpractices2005/sqlserverstringtodate/

[Lethugs]

Hi,
Sorry for late update, it took time for me to figure this out, especially with a lot of work other than programming. Finally found out the problem. It lies on some conditions in my query. Ur suggestions work, Ive converted all the dates to datetime for more reliable result. Making my date column as nvarchar during database design pays a lot of problems to me now.

thank you all for your help. Really appreciate it