[RESOLVED] [C# MVC] DateTime Not Sorting Properly

[FunkySloth]

Hi Guys,

Let me show you the output first:



The data sorted descending, but as you can see, it is not in a correct order. The variable is DateTime Datatype. But why is it sorting that way?

Thank you

[jmcilhinney]

If you have DateTime values and you sort them then they will be sorted correctly. If you don't see the data sorted correctly then you did something wrong. As you haven't shown us what you did, we can't tell you what's wrong with it. If I had to guess, which I do, I'd say that you were sorting by some other value in the same table, either instead of that date column or instead of it.

[KGComputers]

It would be helpful if you can show us the code.

- kgc

[FunkySloth]

Model:

public class ThreadModel
{
        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
	public DateTime? DATE_NEEDED { get; set; }

        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        public DateTime? DATE_FILED { get; set; }

        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        public DateTime? DATE_RECEIVED { get; set; }

        [DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}")]
        public DateTime? DATE_ENDORSED { get; set; }
}

Function:

        public static IList<ThreadModel> GetThreadDates(string username, string status)
        {
            SqlCommand command = new SqlCommand();
            DataTable returnTable = new DataTable();
            List<ThreadModel> returnList = new List<ThreadModel>();

            using (SqlConnection DBConnection = new SqlConnection(DatabaseConnection.LocalConnection))
            {
                command = new SqlCommand("SelectThreadList", DBConnection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@USERNAME", username);
                command.Parameters.AddWithValue("@STATUS", status);

                DBConnection.Open();
                returnTable.Load(command.ExecuteReader());
            }
            
            foreach (DataRow row in returnTable.Rows)
            {
                returnList.Add(new ThreadModel()
                {
                    DATE_NEEDED = (row["DATE_NEEDED"].ToString() == "") ? (DateTime?)null : Convert.ToDateTime(row["DATE_NEEDED"]),
                    DATE_FILED = (row["DATE_FILED"].ToString() == "") ? (DateTime?)null : Convert.ToDateTime(row["DATE_FILED"]),
                    DATE_RECEIVED = (row["DATE_RECEIVED"].ToString() == "") ? (DateTime?)null : Convert.ToDateTime(row["DATE_RECEIVED"]),
                    DATE_ENDORSED = (row["DATE_ENDORSED"].ToString() == "") ? (DateTime?)null : Convert.ToDateTime(row["DATE_ENDORSED"])
                });
            }

            return returnList;
        }

Controller:

        public ActionResult Dashboard(string sortOrder, string currentFilter, int? page, string searchString)
        {
            try
            {
                string searchEncoded = HttpUtility.HtmlEncode(searchString);
				
                ViewBag.CurrentSort = sortOrder;
                ViewBag.DNParam = sortOrder == "dnname_asc" ? "dnname_desc" : "dnname_asc"; // DATE NEEDED
                ViewBag.DFParam = sortOrder == "dfname_asc" ? "dfname_desc" : "dfname_asc"; // DATE FILED
                ViewBag.DRParam = sortOrder == "drname_asc" ? "drname_desc" : "drname_asc"; // DATE RECEIVED
                ViewBag.DEParam = sortOrder == "dename_asc" ? "dename_desc" : "dename_asc"; // DATE ENDORSED
				
                if (searchEncoded != null)
                {
                    page = 1;
                }
                else
                {
                    searchEncoded = currentFilter;
                }
				
                // PAGINATION PARAMETERS.
                ViewBag.CurrentFilter = searchEncoded;
                IQueryable<ThreadModel> monitor = DBCollection.GetThreadDates(HttpContext.Session["USERNAME"].ToString(), "SUBMITTED").AsQueryable();
				
                if (!String.IsNullOrEmpty(searchEncoded))
                {
                    monitor = monitor.Where(s => s.DATE_FILED.ToString().Contains(searchEncoded)
                                              || s.DATE_NEEDED.ToString().Contains(searchEncoded)
                                              || s.DATE_RECEIVED.ToString().Contains(searchEncoded)
                                              || s.DATE_ENDORSED.ToString().Contains(searchEncoded);
                }
				
                switch (sortOrder)
                {
                    case "dfname_desc":
                        monitor = monitor.OrderByDescending(s => s.DATE_FILED);
                        break;
                    case "dnname_desc":
                        monitor = monitor.OrderByDescending(s => s.DATE_NEEDED);
                        break;
                    case "drname_desc":
                        monitor = monitor.OrderByDescending(s => s.DATE_RECEIVED);
                        break;
                    case "dename_desc":
                        monitor = monitor.OrderByDescending(s => s.DATE_ENDORSED);
                        break;
					
                    case "dfname_asc":
                        monitor = monitor.OrderBy(s => s.DATE_FILED);
                        break;
                    case "dnname_asc":
                        monitor = monitor.OrderBy(s => s.DATE_NEEDED);
                        break;
                    case "drname_asc":
                        monitor = monitor.OrderBy(s => s.DATE_RECEIVED);
                        break;
                    case "dename_asc":
                        monitor = monitor.OrderBy(s => s.DATE_ENDORSED);
                        break;
                }
				
                int pageSize = 10;
                int pageNumber = (page ?? 1);
				
                return View(monitor.ToPagedList(pageNumber, pageSize));
            }
            catch (Exception ex)
            {
                HttpContext.Session["ErrorMessage"] = ex.Message;
                return RedirectToAction("Index", "ServerError");
            }
        }

My Stored Procedure sorting the Date Correctly, but upon populating it to DataTable and execute the rest of the procedure, the dates were incorrectly sorted out.

[jmcilhinney]

I would suggest that you debug that controller action and change this:

return View(monitor.ToPagedList(pageNumber, pageSize));

to this:

var model = monitor.ToPagedList(pageNumber, pageSize);
 
return View(model);

so that you can examine the list before it gets sent to the view.

[FunkySloth]

I didn't figure out why its happening, so I made a changes of my Stored Procedure instead.