LinqToSql with dates problem

Question

Hi, I have a simple LinqToSql query

from p in GL_PROJECTs
where p.CREATE_DT == new DateTime(2009,10,26)
select new p

Some of the records in the database have create dates with time parts other than 00:00:00, so wont be retreived by this query. What I want to achieve is a time insensitive retrieval for the whole day.

I tried the following, adding Date, in an attempt to compare like for like:

from p in GL_PROJECTs
where p.CREATE_DT.Date >= new DateTime(2009,10,26)
select new p

but in LinqPad I get the following error:

'System.Nullable' does not contain a definition for 'Date' and no extension method 'Date' accepting a first argument of type 'System.Nullable' could be found (press F4 to add a using directive or assembly reference)

Any ideas why this doesnt work and what I can do to make it work?

Thanks

Answer 1

Just check for the range

from p in GL_PROJECTs 
where p.CREATE_DT >= new DateTime(2009,10,26) && 
      p.CREATE_DT < new DateTime(2009,10,27) 
select new p

Answer 2

If it's nullable you need to use the Value property first, then call Date on that:

p.CREATE_DT.Value.Date

That said, using the Date property will return the date with a timestamp of midnight, so it will still have a time associated with it. That won't be helpful for what you're describing. You can use @AlbertEin's suggestion and check for the range.

Answer 3

If CREATE_DT is allowed to have the NULL value in the database it will be mapped to a Date? type in .net (That is the generic type Nullable<Data>).

You can retrieve the actual Date of a Nullable type by its Value property.

Try

from p in GL_PROJECTs
where p.CREATE_DT.Value.Date == new DateTime(2009,10,26)
select new p

But this will throw an exception if a CREATE_DT is really null. To avoid this exception you have to ensure that the Nullable type really contains a value:

from p in GL_PROJECTs
where p.CREATE_DT.HasValue && p.CREATE_DT.Value.Date == new DateTime(2009,10,26)
select new p