How to get Date without Time from DateTime while join tables

Question

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

DbFunctions.TruncateTime

, 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
anObjective.DateCreation
and
anObjective.DateEnd
?

My desirable result is just Date: 1.9.2016

Edit:

If I write the following syntax:

DateCreation =anObjective.DateCreation.Date,

//or

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(),

anObjective.DateEnd.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.

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

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=>
new
{
    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=>
new
{
    IdObjective = x.IdObjective,
    ObjectiveName = x.ObjectiveName,
    DateCreation = x.DateCreation.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture),
    DateEnd = x.DateEnd.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture)
});

How to get Date without Time from DateTime while join tables

Sign up