LINQ convert datetime to shortdate and order accending

Question

LINQ convert datetime to shortdate and order accending

Peter Newman 66

I am trying to add a list of dates to a viewbag

despite several attempts I'm still not getting anywhere

currently the list that produced from my query is

26/11/2021 00:00:00
25/11/2021 00:00:00
24/12/2021 00:00:00
29/11/2021 00:00:00

I'm trying to get to

24/11/2021
25/11/2021
29/11/2021
24/12/2021

my query

ViewBag.closeddateslist = (From d In db.new_closeddates Order By d.new_date Ascending Select d.new_date).ToList()

Class

Public Property new_date As Nullable(Of Date)

The public property cannot be changed

AgaveJoe 30,126 Reputation points

2021-11-23T14:26:23.543+00:00
I generally use standard DateTime formats in the View in the display logic. That way the model property type stays a DateTime. The official docs DateTime formats.

Custom date and time format strings
Standard date and time format strings

d.new_date.ToString("dd/MM/yyyy")
Peter Newman 66 Reputation points

2021-11-23T14:32:52.103+00:00

Hi AgaveJoe

The reason I'm doing it =this way is the viewbag is to populate a dropdownlist in a vew. There are several dropdowns from various classes

I tried your suggestion but get the following error

ViewBag.closeddateslist = (From d In db.new_closeddates Order By d.new_date Ascending Select d.new_date.ToString("dd/MM/yyyy")).ToList()

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'Char get_Chars(Int32)' method, and this method cannot be translated into a store expression.'
AgaveJoe 30,126 Reputation points

2021-11-23T15:41:09.747+00:00
Sorry, my code example is confusing. I was trying to illustrate how to format a DateTime. Return the query results as a DateTime then convert to a string later not within the LINQ query.

If you must execute the logic in the action then see the following example.

IQueryable<DateTime> Query = from d in _context.Departments where d.StartDate < DateTime.Today select d.StartDate; List<string> dates = Query.ToList().Select(d => d.ToShortDateString()).ToList();
Yijing Sun-MSFT 7,096 Reputation points

2021-11-24T09:38:57.497+00:00

Hi @Peter Newman ,
When you write a LINQ query using Entity Framework it attempts to translate your query into a SQL query. Some .NET operations cannot be translated into SQL. So,you could find the the offending line firstly.
Best regards,
Yijing Sun

Your answer

AgaveJoe 30,126 Reputation points

2021-11-23T14:26:23.543+00:00

I generally use standard DateTime formats in the View in the display logic. That way the model property type stays a DateTime. The official docs DateTime formats.

Custom date and time format strings
Standard date and time format strings

d.new_date.ToString("dd/MM/yyyy")
Peter Newman 66 Reputation points

2021-11-23T14:32:52.103+00:00

Hi AgaveJoe

The reason I'm doing it =this way is the viewbag is to populate a dropdownlist in a vew. There are several dropdowns from various classes

I tried your suggestion but get the following error

ViewBag.closeddateslist = (From d In db.new_closeddates Order By d.new_date Ascending Select d.new_date.ToString("dd/MM/yyyy")).ToList()

System.NotSupportedException: 'LINQ to Entities does not recognize the method 'Char get_Chars(Int32)' method, and this method cannot be translated into a store expression.'
AgaveJoe 30,126 Reputation points

2021-11-23T15:41:09.747+00:00

Sorry, my code example is confusing. I was trying to illustrate how to format a DateTime. Return the query results as a DateTime then convert to a string later not within the LINQ query.

If you must execute the logic in the action then see the following example.

IQueryable<DateTime> Query = from d in _context.Departments where d.StartDate < DateTime.Today select d.StartDate; List<string> dates = Query.ToList().Select(d => d.ToShortDateString()).ToList();
Yijing Sun-MSFT 7,096 Reputation points

2021-11-24T09:38:57.497+00:00

Hi @Peter Newman ,
When you write a LINQ query using Entity Framework it attempts to translate your query into a SQL query. Some .NET operations cannot be translated into SQL. So,you could find the the offending line firstly.
Best regards,
Yijing Sun

Share via

LINQ convert datetime to shortdate and order accending

Your answer