Checking database with date part only on ASP.NET MVC using Entity Framework

Question

I am developing an ASP.NET MVC application. I am stuck by a situation and can't move forward. I will explain my scenario.

I have a date column in my database. I'm using a code-first approach. Now when someone adds a new date using DateTime.Now I want to check the database to count how many rows of data are added with today's date. If it's 5 I want to show an error message to the user who is trying to add new data today. But I can't check today's data as datetime always give result with time.

My date model property is

 public DateTime? Date { get; set; }

Current to code achieve the above is

DateTime date2 = DateTime.Now;

var dateresult = dbcontext.Appointments.Where(q => q.Date == date2).Count();

if (ModelState.IsValid && result == 0 && dateresult <= 5)
{}

but the count doesn't return the result. It is always zero.

Can someone tell me how to achieve this? How to get today's data without comparing time part? I have searched many but couldn't find a suitable answer.

You can check whether the date is between today 00:00 and tomorrow 00.00, if you dont want to format the datetime — Niklas S., Aug 19 '17 at 18:33
cn u please provide me the example code. is there any problem from the dataformat string in model property? — Abhijith, Aug 19 '17 at 18:34
You have posted the definition of `Appointments.AppointmentTime` not `Appointments.Date`? — Niklas S., Aug 19 '17 at 18:36
Good, then you can create two plain `datetimes` with today's and tomorrow's date at 0.00 and change your query to `Where(q => q.Date >= today && q.Date < tomorrow)` — Niklas S., Aug 19 '17 at 18:41
Please see the 2nd answer in the marked duplicate. Also you can simplify your expressions by replacing `Where` with `Count` (*and then remove Count() at the end*) — Igor, Aug 19 '17 at 18:49
Igor I had went through all that answers early but that couldnt resolve my issue. I couldn't get data based on datetime as it always returna time even in datetime.date which is 12.00.00. so i couldnt find a methos that properly removes timepart. but this comparison of 2 dates taht is today n tomorrow solved! thank you guys ! — Abhijith, Aug 19 '17 at 19:10
That is because you are comparing incorrectly. Get only the date first using *Today*: `DateTime date2 = DateTime.Today;` Then compare by truncating the time: `var dateresult = dbcontext.Appointments.Count(q => DbFunctions.TruncateTime(q.Date) == date2);` — Igor, Aug 19 '17 at 19:13
that too worked! :D but in the link you provided there is no answer using appointments.count. anyway thanks. Which of these 2 answer would be best to use? — Abhijith, Aug 19 '17 at 19:26
Use what ever one works best for you, I doubt there is much, if any, difference in performance. — Igor, Aug 19 '17 at 19:39

Ankit · Accepted Answer · 2017-08-19T18:39:10.520

1

Instead of converting DateTime to Date , check if database date falls between today's 12 midnight to tomorrow midnight(exclusive) as shown below

use this :

var today = DateTime.Now.Date;
var tomorrow = today.AddDays(1);

and then this :

 var dateresult = dbcontext.Appointments.Where(q => q.Date >= today && q.Date < tomorrow).Count();

edited Aug 19 '17 at 18:39

answered Aug 19 '17 at 18:30

Ankit

5,733
2
22
23

tried these from other answers but not getting value.. the time property will be 00 or 12 so when searching its searches for time with 00 or 12. – Abhijith Aug 19 '17 at 18:31
i have updated my response ! have a look – Ankit Aug 19 '17 at 18:40
ok let me check – Abhijith Aug 19 '17 at 18:42
that worked!! Thank you so much! – Abhijith Aug 19 '17 at 19:06
DateTime date2 = DateTime.Today; Then compare by truncating the time: var dateresult = dbcontext.Appointments.Count(q => DbFunctions.TruncateTime(q.Date) == date2); this is also right! – Abhijith Aug 19 '17 at 19:28

score 0 · Answer 2 · answered Aug 19 '17 at 18:39

0

Make sure the column name is correct in the Appointment table which is storing the date value for example if the column name is Todaysdate , you have to use below code

var dateresult = dbcontext.Appointments.Where(q => q.Todaysdate == date2).Count();

answered Aug 19 '17 at 18:39

karthik kasubha

392
2
13

that was a mistake. column name is date. i have updated my question. the date column is of type datatime?.. – Abhijith Aug 19 '17 at 18:40
I believe the issue is due to datatype datetime incompatibility between sql and c# , Hope this https://stackoverflow.com/questions/1181662/is-there-any-difference-between-datetime-in-c-sharp-and-datetime-in-sql-server helps – karthik kasubha Aug 19 '17 at 18:47

Checking database with date part only on ASP.NET MVC using Entity Framework

2 Answers2