Foreach Date

.NET
1

Hello,

I have done some research into this but was wondering if anyone could help.

I am running a SQL Query which returns the top 40 rows from a specific table. I’ll post this query below but I appreciate it might not mean a great deal.

SELECT top 40 Title, Description, DateCreated, Slug FROM be_Posts order by DateCreated DESC

This query returns a structure of information, but the column I am interested in is the DateCreated column. An example of this is below (the comma denotes a new row).

2010-12-14 08:30:00.000, 2010-12-14 08:13:00.000, 2010-12-14 07:49:00.000, 2010-12-14 07:19:00.000, 2010-12-13 16:01:00.000, 2010-12-12 18:33:00.000, 2010-12-09 17:44:00.000, 2010-12-06 17:45:00.000, 2010-12-06 11:09:00.000, 2010-12-04 16:53:00.000, 2010-12-03 20:57:00.000, 2010-12-02 14:16:00.000, 2010-12-02 13:31:00.000, 2010-12-02 12:21:00.000, 2010-12-02 11:30:00.000

The above information are dates and times in a particular format. The end result of this project would be to group each date, but then pull the other content from the remaining cells in the row.

To help visualise this:

14th December 2010
– 2010-12-14 08:30:00.000
– 2010-12-14 08:13:00.000
– 2010-12-14 07:49:00.000
– 2010-12-14 07:19:00.000

13th December 2010
– 2010-12-13 16:01:00.000

12th December 2010
– 2010-12-12 18:33:00.000

Of course, within each group the rows are ordered by time. For example, in the group above, for ‘14th December’ the times start with ‘07:19:00.000’ at the bottom and end with ‘08:30:00.000’ at the top. I am unaware which date/time format this is in or even if this is necessary to know.

I was thinking a foreach statement would be appropriate to order the rows into groups and from there by date. In English the statement would be: foreach row, order by date and, only AFTER, order by descending time.

I hope I have explained this well enough to merit support. I would be more than grateful to any replies offering advice.

Thank you,

2

Why order by date and then by time? Why not order by DateTime?

3

Thanks for the reply.

To give this some context, this control would go to the side of an existing website giving updates for the latest news items.

It would look like this: http://img152.imageshack.us/img152/814/newslatest.jpg.

4

I’m sure there is some way to do it with groups but I’ve been out of this for way too long so someone else will hopefully chime in.

Consider this:

protected void Page_Load(object sender, EventArgs e)
{
	IList<DateTime> dates = new List<DateTime>();
	dates.Add(Convert.ToDateTime("12/15/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/16/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/15/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/16/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/19/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/21/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/19/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/21/2010 2:41:27 PM"));

	var daysInList = from date in dates
					orderby date descending
					 select date;
	foreach (var day in (from date in daysInList
					orderby date descending
				   select date.Date).Distinct())
	{
		ltrlDate.Text += "<br />" + day.ToString("MM/dd/yyyy");
		foreach (var dayGroup in daysInList.Where(d=>d.Date == day.Date))
		{
			ltrlDate.Text += "<br />---- " + dayGroup.ToString();
		}
	}
					   
}
5

Just pull the records from the db without considering grouping. Use LINQ GroupBy to group into dates. LINQ groupby will allow you to group by an expression and retain the individual records as IEnumerables within each group.

6

I was sure that was the case. I just can’t remember how. :slight_smile:

Edit to add:
Got it!

protected void Page_Load(object sender, EventArgs e)
{
	IList<DateTime> dates = new List<DateTime>();
	dates.Add(Convert.ToDateTime("12/15/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/16/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/15/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/16/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/19/2010 2:41:27 PM"));
	dates.Add(Convert.ToDateTime("12/21/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/19/2010 2:43:12 PM"));
	dates.Add(Convert.ToDateTime("12/21/2010 2:41:27 PM"));

	var daysInList = from date in dates
					  orderby date descending
					  select date;

	foreach (var day in daysInList.GroupBy(d => d.Date))
	{
		ltrlDate.Text += "<br />" + day.Key.ToShortDateString();
		foreach (var daysInDay in day)
		{
		ltrlDate.Text += "<br />---- " + daysInDay;
		}
	}
}
7

Please excuse the variable names. daysInDay?!?! Yikes. :smiley:

8

Doing it inline, leaving actual data intact, is also an option:

var orderedRecords = from r in records
orderby Convert.ToDateTime(r.CustomDateField) descending
select r;

Then just loop through orderedRecords.

If you wanted to pass on the converted value, select the results into an anonymous type.