CODEWITHSUNDEEP
asp.net-mvc
ZKF3340320
ZKF3340320

Reputation: 181

How to get number of days between two dates in asp.net MVC using Linq

I need to get the number of days between two dates from database using Linq Query, please help me how to do this. thanks

I tried the bellow code but it return me the result nul! please help

 DateTime rightNow = DateTime.Now;

        //int totalDays = (from d in db.vuExpiredProjectsAssigned
        //                 where d.AssignedTo==obj.DepartmentID
        //                 select (d.AssignedDate - d.DueDate).Days).FirstOrDefault();

        //var numberdays = (from pd in db.vuExpiredProjectsAssigned
        //                  where pd.AssignedTo == obj.DepartmentID
        //                  select SqlFunctions.DateDiff("day", pd.AssignedDate, rightNow));

       var result =(from dd in db.vuExpiredProjectsAssigned
                    where dd.AssignedTo==obj.DepartmentID
                    select new
                    {
                        days=SqlFunctions.DateDiff("Day",dd.DueDate,rightNow)
                    });

       ViewBag.ndays = result;

Upvotes: 3

Views: 6192

Answers (3)

Haitham Shaddad
Haitham Shaddad

Reputation: 4456

As long as you are not using the difference in days in the where clasuse, then it is better to do this calculation in your code instead of adding overhead to SQL, so change your code to:

public class democlass
{
 public DateTime DueDate{get;set;} 
 public int count {get; set;}
}

 var result =((from dd in db.vuExpiredProjectsAssigned
            where dd.AssignedTo==obj.DepartmentID
            select new democlass()
            {
                DueDate= dd.DueDate;
            })).ToList();

result.ForEach(a=> {a.count = a.DueDate.Subtract(DateTime.Now).TotalDays});

Upvotes: 0

LateshtClick.com
LateshtClick.com

Reputation: 616

try with this

DbFunctions.DiffDays(dd.DueDate,rightNow).Value

Update

var rightNow= DateTime.Today.ToShortDateString();

public class democlass
{
 public int count {get; set;}
}

and query like this

var result =(from dd in db.vuExpiredProjectsAssigned
                where dd.AssignedTo==obj.DepartmentID
                select new democlass()
                {
                    count = DbFunctions.DiffDays(dd.DueDate,rightNow).Value
                });

then check your result variable.

Upvotes: 0

Jayesh Goyani
Jayesh Goyani

Reputation: 11154

Arithmetic with DateTime is not supported in Entity Framework. You have to use DbFunctions*. So, for the first part of your statement, something like:

var numberdays = ( from p in db.vuExpiredProjectsAssigned
                     where p.AssignedTo == obj.DepartmentID
                     select DbFunctions.DiffDays(p.AssignedDate,p.DueDate));

OR

var numberdays = ( from p in db.vuExpiredProjectsAssigned
                     where p.AssignedTo == obj.DepartmentID
                     select SqlFunctions.DateDiff("day", p.AssignedDate, p.DueDate));

For more reference please check below links.

SqlFunctions Class

DbFunctions Class

Upvotes: 4

Related Questions