Comparing DateTime with ASP.NET MVC and SQL Server

Question

I am trying to create a strongly-typed model and view. This portion of code is supposed to return a list of "emails" sent in a contact-us form.

I want to return all form submissions sent today. The SQL Server 2008 datatype is datetime. So I have a DateTime field in my model.

public ActionResult ResultTable()
{
    CombinedReport report = new CombinedReport();

    report.emails = report.db.ContactEmails.Where(w => w.FormInserted.Equals(DateTime.Today)).ToList();
    return PartialView(report);
}

When I ran this configured for the local built-in SQL Server CE, it worked fine.

When I set it up for our actual database running SQL Server 2008, it doesn't work.

With some trial-and-error, and looking at code in other parts of my program, I noticed that the C# implementation of DateTime is including 12:00:00AM as a part of the date, and when it's doing the comparison to SQL, it's sending that exact time in.

It won't let me compare a DateTime to a string, so I can't even format "today" as a string.

The CombinedReport model I instantiate here is just a view model for a specific page and doesn't directly map to a database ("ContactEmails" does).

What is the best way to go forward that will let me keep a strongly-type view and model?

Look at using Convert.ToDateTime to get your string date + string time into one datetime — Limey
– Limey, Commented May 15, 2012 at 13:54

Jon Skeet · Accepted Answer · 2012-05-15 13:56:20Z

1

If you only want to compare the date part of the value, you should make that explicit:

DateTime today = DateTime.Today;
report.emails = report.db.ContactEmails
                         .Where(w => w.FormInserted.Date == today)
                         .ToList();

Now whether the flavour of LINQ you're using supports the Date property is a different matter - but that's what I'd try to start with.

answered May 15, 2012 at 13:56

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

coreno Over a year ago

I'm using whatever the default LINQ implementation is with VS2008 and MVC3. It doesn't appear to support the Date property

Jon Skeet Over a year ago

@coreno: I don't believe there's any such thing as a "default LINQ implementation" - you should know whether you're using LINQ to SQL or the Entity Framework.

Jeff Ogata · Accepted Answer · 2012-05-15 14:29:04Z

1

If you are using EF, use the EntityFunctions.TruncateTime method:

.Where(w => EntityFunctions.TruncateTime(w.FormInserted).Equals(DateTime.Today))

answered May 15, 2012 at 14:29

Jeff Ogata

58k20 gold badges117 silver badges128 bronze badges

Collectives™ on Stack Overflow

Comparing DateTime with ASP.NET MVC and SQL Server

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related