Return only date from SQL Server CE datetime using linq-to-entities

Question

Is there a way to get a linq query to return just the date portion of a datetime. I've tried all the suggestions I can find, all of which generate errors at runtime. This is just the select portion of the query along with each error message (the rest of the query works great):

select new { d.id, d.date.Value.Date };

The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

select new { d.id, date=EntityFunctions.TruncateTime(d.date) };

The function is not recognized by SQL Server Compact. [ Name of function = TRUNCATETIME,Data type (if known) = ]

select new { d.id, date=d.date.Value.Month + "/" + d.date.Value.Day };

Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.

select new { d.id, date=d.date.Value.Month.ToString() + "/" + d.date.Value.Day.ToString() };

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

select new { d.id, date=String.Concat(d.date.Value.Month, "/")};

LINQ to Entities does not recognize the method 'System.String Concat(System.Object, System.Object)' method, and this method cannot be translated into a store expression.

Thanks for any help!

MarcinJuraszek MarcinJuraszek · Accepted Answer · 2013-09-21T19:11:34

select new {
    d.id,
    date = SqlFunctions.DatePart("m", d.date) + "/" + SqlFunctions.DatePart("d", d.date)
};

It will give you a string with month/day pattern.

But I would go with following:

(from r in row
...
select new { d.id, date = d.date.Value })
.AsEnumerable()
.Select(x => new { x.id, x.date.Date });

It would select whole datetime from db and move date-only selection into LINQ to objects query.

Return only date from SQL Server CE datetime using linq-to-entities

1 Answers