How can i group by a datetime field only using the date without the time? RSS

• • Reply
  

  johnjohn1231...

  Participant

  929 Points

  885 Posts

  How can i group by a datetime field only using the date without the time?

  May 10, 2012 09:35 PM|LINK

  I have a Date property with a Datetime Datatype currently i need to count the number of records in each day regardless of the time i wrote the following:-

  public IQueryable<VisitSearch> visitsearch(DateTime? datefrom, DateTime? dateto, int countryid)
  
              {              var vs = (from v in entities.Visits
                            where ((v.Date >= datefrom || datefrom == null) && (v.Date <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                            group v by v.Date into groupvisit
                            select new VisitSearch
                            {
                                VisitDate = groupvisit.Key,
                                count = groupvisit.Count()       });
                  return vs;

  But currently the result will contain different groups that have the same Date, for example if i have Five objects with 1/April/2012 date then i will get five different records with a count of 1 ,,, instead of getting one record with a count of 5.

  I tried to add .Date to the V.Date in the above method, but i got the following error :-

  The specified type member 'Date' is not supported in LINQ to Entities

  var vs = (from v in entities.Visits
                            where ((v.Date.Date >= datefrom || datefrom == null) && (v.Date.Date <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                            group v by v.Date into groupvisit
                            select new VisitSearch
                            {
                                VisitDate = groupvisit.Key,
                                count = groupvisit.Count()
  
                            });
                  return vs;

  
  
  

  • Edited by johnjohn123123 on May 10, 2012 09:51 PM
  • Edited by johnjohn123123 on May 10, 2012 09:53 PM
• • Reply
  

  abdu292

  Participant

  1555 Points

  372 Posts

  Re: How can i group by a datetime field only using the date without the time?

  May 11, 2012 07:03 AM|LINK

  Try your code, in the following way. I tried it in a sample and it works.

  (Below code taken from my sample application.)

  var tt =
                  from t in db.Test1s
                  let dt = t.MyDate
                  group t by new { y=dt.Year, m=dt.Month,d=dt.Day} into dtd
                  select new
                  {
                      dtgrp = dtd.Key,
                      dtcnt = dtd.Count()
                  };

  Don't forget to "Mark As Answer" if a post helps you.
  
  With best regards,
  
• • Reply
  

  DenisM

  Member

  124 Points

  45 Posts

  Re: How can i group by a datetime field only using the date without the time?

  May 11, 2012 07:44 AM|LINK

  In case of linq to entities, and if you need to truncate time, try to use

  System.Data.Objects.EntityFunctions.TruncateTime(v.Date)

  
  
  

  • Edited by DenisM on May 11, 2012 07:44 AM
  • Edited by DenisM on May 11, 2012 07:44 AM
• • Reply
  

  johnjohn1231...

  Participant

  929 Points

  885 Posts

  Re: How can i group by a datetime field only using the date without the time?

  May 12, 2012 12:58 AM|LINK

  DenisM

  In case of linq to entities, and if you need to truncate time, try to use

  System.Data.Objects.EntityFunctions.TruncateTime(v.Date)

  thanks for the reply,, i tried the below but i got the following error "Error    12    Argument 1: cannot convert from 'System.DateTime?' to 'System.DateTime' " on VisitDate = groupvisit.Key,:-

   public IEnumerable<VisitSearch> visitsearch(DateTime? datefrom, DateTime? dateto, int countryid)
              {
                  
                  var vs = (from v in entities.Visits
                            where ((System.Data.Objects.EntityFunctions.TruncateTime(v.Date) >= datefrom || datefrom == null) && (System.Data.Objects.EntityFunctions.TruncateTime(v.Date) <= dateto || dateto == null) && (v.Patient.NationalityID == countryid || countryid == 0))
                            group v by System.Data.Objects.EntityFunctions.TruncateTime(v.Date) into groupvisit
                            select new VisitSearch
                            {
                                VisitDate = groupvisit.Key,
                                count = groupvisit.Count()
  
                            });
                  return vs;
                       
              
              }

  so what might be the problme ,, baring in mind that the V.Date is of type Datetime and not Datetime?...

  Updated:-

  i checked the following documentation http://msdn.microsoft.com/en-us/library/dd395596.aspx ,,and it mentioned that the return type for the EntityFunctions.TruncateTime is

  Return Value

  Type: System.Nullable<DateTime>
  The input date with the time portion cleared.

  BR
  
  

  • Edited by johnjohn123123 on May 12, 2012 01:27 AM
  • Edited by johnjohn123123 on May 12, 2012 01:47 AM
• • Reply
  

  DenisM

  Member

  124 Points

  45 Posts

  Re: How can i group by a datetime field only using the date without the time?

  May 14, 2012 06:39 AM|LINK

  Maybe it should be grouped by value of that nullable datetime

  group v by System.Data.Objects.EntityFunctions.TruncateTime(v.Date).Value

  something like this

  or

  VisitDate = groupvisit.Key.Value

  
  
  
  

  • Edited by DenisM on May 14, 2012 06:40 AM
  • Edited by DenisM on May 14, 2012 06:42 AM
• • Reply
  

  asteranup

  All-Star

  30184 Points

  4906 Posts

  Re: How can i group by a datetime field only using the date without the time?

  May 14, 2012 10:56 AM|LINK

  Hi,

  Check this-

  http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/cb58ae5f-5db9-4054-92b6-a1bf63764574

  Thanks & Regards
  Anup Das Gupta
  Mark as Answer if you feel so. Visit My Blog
  • Marked as answer by Young Yang - MSFT on May 17, 2012 03:25 AM