0

I have a SQL query like this:

SELECT date FROM cases
 WHERE  date BETWEEN '11/01/2015' AND '11/15/2015'

I want to do the same thing in MVC Controller using Linq. So what I have is this:

DateTime today = DateTime.Today;
var case = _dataContext.tb_cases;
var result=(from c in case
            select new
            {
            date=c.date
            ...
            }).Where(p=>p.date >= new DateTime(2015,11,01)&& p.date < today).ToList();
return Json(result, JsonRequestBehavior.AllowGet);

But somehow I couldn't get any result when comparing the date between new DateTime(2015,11,01) and today, and I do have data returned when I use SQL query in my database. I'd also like to set up my date format as same as those two in SQL query.

Anyone can help me to solve this problem?

Thank you very much!

Kevin

QIWEN HU
  • 239
  • 2
  • 8
  • 19
  • Are you saying your not getting any result, or just not all the results you expect –  Nov 16 '15 at 23:19
  • I find the best way to debug this kind of questions is to run up a sql profiler and capture the sql being sent to SQL server. Then compare it with your own and figure out why the results are different. Not the fastest way, but worked every time for me. – Andrew Savinykh Nov 16 '15 at 23:29
  • Additionally, I'm 90% sure that if you try to come up with a minimal program that reproduces your result, you find your problem before you finish. http://stackoverflow.com/help/mcve – Andrew Savinykh Nov 16 '15 at 23:32
  • @Stephen Muecke I'm not getting any result. There should be some result when I run the same query in Sql management. But somehow Linq couldn't recognize the datetime range, because I tried to use other ids and names in linq in where clause and successfully returned the data I expect. – QIWEN HU Nov 16 '15 at 23:43
  • @zespri The linq in controller returns the Date like this: `{11/16/2015 12:00:00 AM}` But when I check my Json result in webpage it becomes to `"/Date(1423807200000)/"` and I dont even know how json transfers date to this format. I think if I can't find where the problem is till tomorrow, I'll test it in a smaller program. – QIWEN HU Nov 16 '15 at 23:56
  • Based on your last comment your are getting the values. Your problem is with understanding the json date format. If you wanting to convert the value to a 'readable' date on the client, then either format the date to a string before your send it or convert it on the client - refer [this answer](http://stackoverflow.com/questions/726334/asp-net-mvc-jsonresult-date-format) –  Nov 17 '15 at 00:29

1 Answers1

-2

Try :

Where(p=> (DateTime.Compare(p.date,DateTime.Parse("2015/11/01").Date))<0 && (DateTime.Compare(p.date,DateTime.Today)<0)).ToList();

To know how to use DateTime.Parse check here.

Hadee
  • 1,392
  • 1
  • 14
  • 25
  • Seems like Linq or C# doesn't like the code to be written in this way... It says" The best overloaded method match for 'System.DateTime.Compare(System.DateTime,System.DateTime)'has some invalid arguments" when I mouse over `DateTime.Parse("2015/11/01").Date` – QIWEN HU Nov 17 '15 at 00:08
  • Probably you need to check details in how to use DateTime.Parse . Check link I gave on "here" – Hadee Nov 17 '15 at 00:11