• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2799
  • Last Modified:

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

0
ToString1
Asked:
ToString1
  • 3
  • 3
  • 2
  • +2
1 Solution
 
Imran Javed ZiaCommented:
you can try some thing like
DateTime.MinValue == dateCreated
0
 
Imran Javed ZiaCommented:
alternatively make your variable nullable as follwoing and then you will be able to check if it is null or not
DateTime ? dateCreated;
0
 
ToString1Author Commented:
The datetime is already set as nullable and I cannot set the datetime to min value when created.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Pratima PharandeCommented:
try this

select * from tablename

where dateCreated is null
0
 
Imran Javed ZiaCommented:
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
 
BardobraveCommented:
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
 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now