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

linq problem with nullable datetime when returning data with a view

Hi
In the code below I am returning data with linq using a sql server view.
The DOB field is nullable and works fine if a date is entered in the database table however it returns nothing if the DOB field is null in database.

This forms part of a dynamic query where user can enter optional criteria.  So if they do not enter DOB is defaults to "1900, 1, 1"

The DOB field needs to be nullable
var Data  =  (from qryReport in this.viewReportsData                           
                           && qryReport.DOB >= DOB
                           select qryReport);

Open in new window

0
mugsey
Asked:
mugsey
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
mugseyAuthor Commented:
Forgot to say the above DOB database field is of type      smalldatetime
0
 
Meir RivkinFull stack Software EngineerCommented:
the default value u get (1/1/1990) is SQLBaseDate of SqlDateTime structure.
unfortunately, it's not a public property so I'd suggest use a constant to validate the DOB return.
for example:

const DateTime SQL_BASE_DATE = DateTime.Parse("1/1/1990");

if the return equals SQL_BASE_DATE then u can return null inside the linq.
0
 
Fernando SotoCommented:
Hi mugsey;

The variable DOB should be defined with the DateTime? and not a DateTime. For example

DateTime? DOB = null;

Fernando
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Fernando SotoCommented:
The data type DateTime? stands for a nullable DateTime.
0
 
mugseyAuthor Commented:
OK guys thanks

So maybe all I need to do is to add

&& qryReport.DOB >= DOB  && qryReport.DOB == null

So regardless it will bring back all nulls within the query?
0
 
kumar754Commented:
this doesnt make any sense:
(qryReport.DOB >= DOB  && qryReport.DOB == null )

you do this:
DateTime? DOB = "somedate";
and in your query >>
(qryReport.DOB == null || qryReport.DOB >= DOB)
0
 
Meir RivkinFull stack Software EngineerCommented:
@mugsey

as i posted earlier:

const DateTime SQL_BASE_DATE = DateTime.Parse("1/1/1990");
(qryReport.DOB == null || qryReport.DOB >= SQL_BASE_DATE)
0
 
mugseyAuthor Commented:
OK thanks guys
0

Featured Post

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.

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