StepUp StepUp - 1 year ago 154
C# Question

How to get Date without Time from DateTime while join tables

I am joining three tables and I would like to get date without time.

var query = from anObjective in db.Objective
join anObjectiveType in db.ObjectiveType
on anObjective.IdType equals anObjectiveType.IdObjectiveType
join statusCode in db.StatusCode
on anObjective.IdStatusCode equals statusCode.IdStatus
select new
IdObjective = anObjective.IdObjective,
ObjectiveName = anObjective.ObjectiveName,
DateCreation = anObjective.DateCreation, //get just Date without time
DateEnd = anObjective.DateEnd, //get just Date without time


I know there is a method
, but it returns time with zero values. But I want to get just date without time.

I've also seen this answer, but I cannot figure out how to apply when joining, not grouping.

Model class:

public partial class Objective
public int IdObjective { get; set; }
public string ObjectiveName { get; set; }
public Nullable<System.DateTime> DateCreation { get; set; }
public Nullable<System.DateTime> DateEnd { get; set; }

How to get Date value without time for properties such as

My desirable result is just Date: 1.9.2016


If I write the following syntax:

DateCreation =anObjective.DateCreation.Date,
DateCreation =null ? default(DateTime?) : anObjective.DateCreation.Date,

then I've got a such exception:

'System.Nullable' does not contain a definition for
'Date' and no extension method 'Date' accepting a first argument of
type 'System.Nullable' could be found (are you
missing a using directive or an assembly reference?)

If I write:

DateCreation =anObjective.DateCreation.Value.ToShortDateString(),

then I've got a such exception:

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

If I write:

DateCreation =anObjective.DateCreation.Value.Date,

then I've got a such exception:

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

Answer Source

First, recognize that there is no built-in type for a date without a time in .NET. At least not yet anyway. So if you want date-only, then you'll need it in a string in some particular format.

You're not going to be able to get the underlying provider of a LINQ query to understand the conversion of DateTime to string. So, you'll need to query for the data in its original form, and then convert it to string after the query results are materialized.

Start with your original query, unmodified from what you showed at the top of your question. Then add the following:

var results = query.AsEnumerable().Select(x=>
    IdObjective = x.IdObjective,
    ObjectiveName = x.ObjectiveName,
    DateCreation = x.DateCreation.ToShortDateString(),
    DateEnd = x.DateEnd.ToShortDateString()

You have some options also:

  • If you know you want a List or an Array, then you can use ToList() or ToArray() instead of AsEnumerable().

  • If you want the resulting string to be in a specific format, or use a specific culture, then you can use ToString instead of ToShortDateString

For example, you might want an array containing a standard ISO-8601 date string and you might want to use the Invariant culture to avoid side effect when the current culture uses a non-Gregorian calendar system.

var results = query.ToArray().Select(x=>
    IdObjective = x.IdObjective,
    ObjectiveName = x.ObjectiveName,
    DateCreation = x.DateCreation.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
    DateEnd = x.DateEnd.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download