Stem Step Stem Step - 4 months ago 21
C# Question

How to subtract two dates in LINQ to SQL?

I want to be able to add dd to custQuery7. Aka, subtract two dates date_paid from trans_date where the value is greater than 3 days old. Then count those.

var dd = m.date_paid.Value.Subtract(m.trans_date.Value) > 3;

var custQuery7 = ((from m in DataContext.pu_balance_hists
where m.desc_code == 141
&& m.trans_date.Value.Year == 2016
&& m.trans_date.Value.Month == 5
select m)).Count();


Here is the SQL query I'm trying to mimic:

select COUNT(*) as 'UnprocessedChques' from pu_balance_hist
where desc_code=141 and date_paid-trans_date>3
and YEAR(trans_date)=2016 and MONTH(trans_date)=5

Answer

Not tested, but can you do something like:

var custQuery7 = ((from m in DataContext.pu_balance_hists
                       where m.desc_code == 141 
                       && SqlMethods.DateDiffDay(m.date_paid, m.trans_date) >= 3
                       && m.trans_date.Value.Year == 2016
                       && m.trans_date.Value.Month == 5
                       select m)).Count();

https://msdn.microsoft.com/en-us/library/bb468730(v=vs.110).aspx