Linq - Select Date from DateTime

Question

I have a Linq Result from which I need to select only Date from DateTime.
My Query goes like this :

var UserTemplates = (from xx in VDC.SURVEY_TEMPLATE
                     where xx.USER_ID == userid && xx.IS_ACTIVE == 1
                     select new
                     {
                       xx.TEMPLATE_ID,
                       xx.TEMPLATE_NAME,
                       //CREATED_DATE = xx.CREATED_DATE.Value.Date
                       //CREATED_DATE = EntityFunctions.TruncateTime(xx.CREATED_DATE)
                       xx.CREATED_DATE
                     }).ToList();

Is that Possible? Any help would be greatly appreciated.

CREATED_DATE - `datetime` datatype

Actually,I'm Binding it to a Control as a DataSource and the control is displaying both Date and Time.But I want to display only date.

When I'm trying with CREATED_DATE = xx.CREATED_DATE.Value.Date,It is giving an error like :

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

xx.CREATED_DATE.ToShortDateString() ? What format is CREATED_DATE? — andrewb, Sep 26 '13 at 07:11

Kaf · Accepted Answer · 2015-06-23T10:31:50.307

29

If it is for presentation purpose, then you can use DataFormatString property. For Example, if you are binding a datasource to a GridView, you could do as;

<asp:BoundField DataField="CREATED_DATE" ...
     DataFormatString="{0:d}" ../>

Else you can use EntityFunctions.TruncateTime() which returns the input date without the time portion.

EntityFunctions.TruncateTime(xx.CREATED_DATE)

Your query would be like;

var UserTemplates = (from xx in VDC.SURVEY_TEMPLATE
                     where xx.USER_ID == userid && xx.IS_ACTIVE == 1
                     select new
                     {
                       xx.TEMPLATE_ID,
                       xx.TEMPLATE_NAME,
                       EntityFunctions.TruncateTime(xx.CREATED_DATE) //new like
                     }).ToList();

edited Jun 23 '15 at 10:31

answered Sep 26 '13 at 07:17

Kaf

33,101
7
58
78

11

In the newer versions of Entity Framework, `EntityFunctions` has been replaced by `DbFunctions`. – Tot Zam Oct 15 '15 at 20:32
and in Entity Framework Core neither EntityFunctions nor DbFunctions is needed, date fields handle .Date nicely – blomster Sep 02 '20 at 16:15

score 2 · Answer 2 · answered Mar 16 '18 at 11:44

Use this...It worked for me..

var UserTemplates = (from xx in VDC.SURVEY_TEMPLATE
                     where xx.USER_ID == userid && xx.IS_ACTIVE == 1
                     select new
                     {
                       xx.TEMPLATE_ID,
                       xx.TEMPLATE_NAME,
                       CREATED_DATE=SqlFunctions.DateName("day", xx.CREATED_DATE).Trim() + "/" +
        SqlFunctions.StringConvert((double)xx.CREATED_DATE.Value.Month).TrimStart() + "/" +
        SqlFunctions.DateName("year", xx.CREATED_DATE)
         }).ToList();

And Output Date will be dd/MM/yyyy format

Dbuggy · Answer 3 · 2013-09-26T07:45:04.290

0

Perhaps this ?

var qry = (from xx in VDC.SURVEY_TEMPLATE
           where xx.USER_ID == userid && xx.IS_ACTIVE == 1
           select new { xx.TEMPLATE_ID, xx.TEMPLATE_NAME, xx.xx.CREATED_DATE });

var UserTemplates = qry.AsEnumerable().Select(xx => new 
                    {
                       xx.TEMPLATE_ID,
                       xx.TEMPLATE_NAME,
                       xx.CREATED_DATE.Value.Date 
                    }).ToList();

edited Sep 26 '13 at 07:45

answered Sep 26 '13 at 07:11

Dbuggy

901
8
16

It doesn't have any property like '.Date'. It only has property like '.Value.Date' and that's not working and giving an error like : The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported. – RealSteel Sep 26 '13 at 07:17
@RealSteel This, should work if you follow it completely. The reason being is that the `.Date` call is running in Linq to Object rather than Linq to Entities. Still. It isn't my preferred solution. – Aron Sep 26 '13 at 08:09
1

@Aron It is also not my preferred solution. If the value is obtained for display purposes i wouldn't even care to make it a date from within the query at all. The control should have a FormatString for the display value. – Dbuggy Sep 26 '13 at 08:18
Getting this error: {"The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported."} – Girish Gupta Aug 31 '15 at 13:08

Damith · Answer 4 · 2013-09-26T07:56:55.397

0

var UserTemplates = (from xx in VDC.SURVEY_TEMPLATE
                     where xx.USER_ID == userid && xx.IS_ACTIVE == 1 select  xx).AsEnumerable()
                     .Select( i=> new
                     {
                       i.TEMPLATE_ID,
                       i.TEMPLATE_NAME,
                       CREATED_DATE = i.CREATED_DATE.ToString("M/d/yyyy"),
                       i.CREATED_DATE
                     }).ToList();

edited Sep 26 '13 at 07:56

answered Sep 26 '13 at 07:26

Damith

62,401
13
102
153

1

A query body must end with a select clause or a group clause – RealSteel Sep 26 '13 at 07:36

Karthic G · Answer 5 · 2018-09-21T09:00:25.647

Try this,

var UserTemplates = (from xx in VDC.SURVEY_TEMPLATE
                 where xx.USER_ID == userid && xx.IS_ACTIVE == 1
                 select new Attributes
                 {
                   TEMPLATE_ID=xx.TEMPLATE_ID,
                   TEMPLATE_NAME=xx.TEMPLATE_NAME,
                   dateCreatd = xx.CREATED_DATE                      
                 })
                 .AsEnumerable()
                 .select(p=>new Attributes
                 {
                   TEMPLATE_ID =p.TEMPLATE_ID,
                   TEMPLATE_NAME=p.TEMPLATE_NAME,
                   dateString = p.dateCreatd .Value.toString("YYYY-MMM-dd")                       
                 }).ToList();


public class Attributes
{
 public string TEMPLATE_ID { get; set; }
 public string TEMPLATE_NAME { get; set }
 public DateTime dateCreatd { get; set; }
 public string dateString { get; set; }

}

hopes it is useful to you

Linq - Select Date from DateTime

5 Answers5

Linked