Linq MAX returns null on DateTime Type

You could try using a null-coalescing operator (??).

http://msdn.microsoft.com/en-us/library/ms173224%28v=VS.100%29.aspx

For example, see the code snippet below:

System.Nullable<DateTime> DateToSelect = (from p in db.Archived select p.TestDate).Max() ?? new DateTime(1900, 1, 1);

Or,

DateTime? DateToSelect = (from p in db.Archived select p.TestDate).Max() ?? new DateTime(1900, 1, 1);

Select allOpen in new window

Hi

Both suggestions returned errors

Operator '??' cannot be applied to operands of type 'System.DateTime' and 'System.DateTime

OK.  You could try the following:

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }

Or,

DateTime? DateToSelect = (from p in db.Archived
                                            where p.TestDate is not null
                                            select p.TestDate).Max()
                       

Try this as well:

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate;
                       }) ?? (object)DBNull.Value;

Ok used this one but still gives the same error if there are no recods in the table

DateTime? DateToSelect = (from p in db.Archived
                                            where p.TestDate is not null
                                            select p.TestDate).Max()
Rewrote
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null select p.TestDate).Max();

Also works with the second condition included
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && ID = 1 select p.TestDate).Max();


This one i couldnt work out the syntax mistakes Could you have another look please :)

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = (DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }

Thanks
John

Hi johngrant_ks;

To your question:

I'm trign to return a date from a table but if there are no records in the table i get the Error The null value cannot be assigned to a member with type System.DateTime which is a non-nullable value type. How do i stop this null error if there are no records matching the criteria

The statement you have posted, shown below, will correctly handle a table which has no records or one that has data in it but the TestDate column has no values in any row, which will return a null. This is also true if the query had a where clause in it. Although the sample query you posted with the where clause had a syntax error in it, you had this, where p.ID = 1, and it should have been this where p.ID == 1.

System.Nullable<DateTime> DateToSelect = 
    (from p in db.Archived 
     select p.TestDate).Max();

Select allOpen in new window

I am saauming this is Linq to SQL and .Net 4.0.

Fernando

Just missed an opening parenthesis.  I just typed this as I go along.  :-)

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = ((DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       }

Sorry, that last line in my post should have read as follows:
I am assuming this is Linq to SQL and .Net 4.0.

Oops.  I think I missed another ending parenthesis.

DateTime? DateToSelect = (from p in db.Archived
                       select new {
                               TstDate = ((DateTime?) p.TestDate).Single().Max() ?? new DateTime(1900, 1, 1);
                       });

Also, have a look at this link.  This might help.

http://weblogs.asp.net/scottgu/archive/2007/09/20/the-new-c-null-coalescing-operator-and-using-it-with-linq.aspx

Also as @FernandoSoto already mentioned, this

Also works with the second condition included
DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && ID = 1 select p.TestDate).Max();

Should be,

DateTime? DateToSelect = (from p in db.Archived where p.TestDate != null && p.ID == 1 select p.TestDate).Max();

Hi FernandoSoto/Alfred1

Alfred, still get errors tring to get your code going you may wan to try doing in VS, it maybe more than just a missing parenthesis.

FernandoSoto, If i delete the rows from the table and try and run it i still get the same error


Problem1.jpg

Hi johngrant_ks;

The image resolution is very low and all that can be seen from the characters is pixelation, non readable.

A couple of questions:
What version of Visual Studio, 2008, 2010?
Is this Linq to SQL or Linq to Entity Framework?
Post the actual code and not just the query please?
Is the DateTime field in the database allows nulls?

Fernando

You vcan click on the image to see it better

What version of Visual Studio, 2008, 2010? VS 2010
Is this Linq to SQL or Linq to Entity Framework? LINQ to SQL
Is the DateTime field in the database allows nulls? No

This is all the Code
qtxtDataContext db = new qtxtDataContext();
DateTime? DateToSelect = (from p in db.qtxt_Competitions_Archived_Winners select p.WinningDate).Max();

lblDateLastDrawn.Text = DateToSelect.ToString();

OK.  Out of curiosity,  I tried to reproduce this (LINQ to SQL) in my Test Server and it seems to be working fine.  Please note that the datetime column I tested allows null.  

protected void Page_Load(object sender, EventArgs e)
        {
            TestDataContext TestData1 = new TestDataContext();

            DateTime myDate = (DateTime)DateTime.Now;

            //Both of the following works. Max date shown.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date 
                                      select p.Status_Updated_Date).Max();

            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date != null
                                      select p.Status_Updated_Date).Max();

            //This works. Nothing is shown and no error.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date == null
                                      select p.Status_Updated_Date).Max();

            //I also tried to return zero rows and it works just fine.
            DateTime? DateToSelect = (from p in TestData1.SAMPLEs 
                                      where p.Status_Updated_Date > myDate
                                      select p.Status_Updated_Date).Max();

            Response.Write(DateToSelect);

        }

Select allOpen in new window

Hi thanks to you both Alfred and Fernando

I'm not sure why it would work on your test server alfred and not on my instance. Maybe Fernando could let us know why, as the solution he has found has fixed my issue.

Thanks Again
John

Oh, I didn't notice in your response that the column you are working on DOES NOT ALLOW NULLS.

The reason why you are getting that error because DateToSelect is set as nullable.

Fernando's solution cast it to nullable DateTime and that is why it works.

Actually if you do the following, it might just work fine.

DateTime DateToSelect = (from p in db.Archived select p.TestDate).Max();

Hi Alfred1;

The Nullable type can be assign a value or in the absents of any value a null. So the DateTime? can hold a valid DateTime value or can have a null assigned to it. For example if you have the following:

// A Nullable DateTime variable 
DateTime? dt;

// Then you assign to dt a Nullable type a DateTime value 
// it will have no problem storing that value in dt.
dt = DateTime.Today;

// And of course you can also assign a null value to a Nullable type
dt = null;

Select allOpen in new window

So where does the problem come in. The Max operator is defined as follows :

public static TSource Max<TSource>(
      this IQueryable<TSource> source
)

In our case the Max method is expecting a DateTime which by definition has to have a value because objects of this type are NOT Nullable. But when it receives an null object it immediately throws an exception. By casting it to a Nullable type in the Max method it returns it immediately as a null.

I hope that helps.

Fernando

Sorry johngrant_ks my last post should have had your username on it and not Alfred1.

@FernandoSoto,

I was actually thinking awhile ago if I posted a question.  LOL. :-)

Anyway, Thanks, Mate!  I know it already as I summarized it in ID:35769969 but explanation appreciated.

@FernandoSoto,

Oops.  Wrong ID, it should be ID:35767144  :-)