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

Question

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;

the error is " DbArithmeticExpression arguments must have a numeric common type." — ZKF3340320
– ZKF3340320, Commented Oct 9, 2016 at 7:49

Jayesh Goyani · Accepted Answer · 2016-10-09 07:56:32Z

4

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

edited Oct 9, 2016 at 7:56

answered Oct 9, 2016 at 7:52

Jayesh Goyani

11.2k11 gold badges32 silver badges50 bronze badges

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

7 Comments

ZKF3340320 Over a year ago

it return an error " No overload for Method 'diffDays' takes 1 arguments " ??

ZKF3340320 Over a year ago

what it retuns? i store the result in ViewBag it print me the query itself

Jayesh Goyani Over a year ago

it should return int array. Could you please put the break point and check that?

ZKF3340320 Over a year ago

SELECT DATEDIFF(day, [Extent1].[AssignedDate], [Extent1].[DueDate]) AS [C1] FROM [dbo].[vuExpiredProjectsAssigned] AS [Extent1] WHERE [Extent1].[AssignedTo] = @p__linq__0

Jayesh Goyani Over a year ago

could you please convert your result into list

|

LateshtClick.com · Accepted Answer · 2016-10-11 06:23:34Z

0

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.

edited Oct 11, 2016 at 6:23

answered Oct 11, 2016 at 5:35

LateshtClick.com

6164 silver badges5 bronze badges

9 Comments

ZKF3340320 Over a year ago

var result = (from dd in db.vuExpiredProjectsAssigned where dd.AssignedTo == obj.DepartmentID select DbFunctions.DiffDays(dd.AssignedDate, dd.DueDate).Value); ViewBag.ndays = result;

ZKF3340320 Over a year ago

still the return result is null

LateshtClick.com Over a year ago

days is anonymous field?

ZKF3340320 Over a year ago

i have a date stored in database using short format. ex . 2016-1-1,

ZKF3340320 Over a year ago

i did but still result: null

|

Haitham Shaddad · Accepted Answer · 2016-10-11 11:01:35Z

0

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});

answered Oct 11, 2016 at 11:01

Haitham Shaddad

4,4662 gold badges16 silver badges23 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related