linq to sql check for null datetime

In sql server table I have datetime column which can be nullable.

In linq lambda query I want to check for an ID and that the datetime is null

How can I do this?

I know that doing  dateCreated  != null  will always be true

ToString1Asked:
Who is Participating?
 
BardobraveConnect With a Mentor Commented:
Mmmmm.... somewhat strange....

Customer.Where(o => o.typeID == 22  && o.approvalDate.Value != null).Count() > 0;
and
Customer.Where(o => o.typeID == 22  && o.approvalDate.Value is not null).Count() > 0;

should do the same and both should work... If approvalDate is a datetime value maybe your system is storing a "default" value when no data is received instead of letting the field null.

Have you checked that the field is null on the database? (maybe it has a value like 01/01/0001?)
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
you can try some thing like
DateTime.MinValue == dateCreated
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
alternatively make your variable nullable as follwoing and then you will be able to check if it is null or not
DateTime ? dateCreated;
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ToString1Author Commented:
The datetime is already set as nullable and I cannot set the datetime to min value when created.
0
 
Pratima PharandeCommented:
try this

select * from tablename

where dateCreated is null
0
 
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
then try the follwoing in linq expression

dateCreated.HasValue
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
... where ID='your ID'
and dateCreated is NULL

above is plain sqlplus, not sure if that will work in LINQ (not familiar with that, sorry)
0
 
ToString1Author Commented:
Iinq this is my query, it is to select a customer record that has the nullable dateime field approval date not null

Customer.Where(o => o.typeID == 22  && o.approvalDate.Value != null).Count() > 0;

When I do this it always returns true even when there is nothing in approvalDate column
0
 
Pratima PharandeCommented:
Customer.Where(o => o.typeID == 22  && o.approvalDate.Value is not null).Count() > 0;
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Think the issue is probably that NULL values cannot be compared or counted with.

What if you change to:

Customer.Where(o => o.typeID == 22  &&  nvl(o.approvalDate.Value, 0)  != 0).Count() > 0;

Assuming nlv is supported, in case NULL is found, 0 is substituted and the compare is now to (number) 0 instead of NULL value.
0
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Any comment on how you got it working and why you accepted answer from Bardobrave?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.