Click here to Skip to main content
14,241,762 members
Rate this:
Please Sign up or sign in to vote.
Hello,
I have a little problem, i want get all records in datatable between two dates and time. Please look at the code bellow. I want to get all the records between "25/5/2019 21:43:24 PM" AND "25/5/2019 05:32:42 AM". What can i do please advise.
Thanks in advance.

What I have tried:

DataTable table = new DataTable();
		table.Columns.Add(new DataColumn("ShiftStartDateTime", typeof (DateTime)));
		table.Columns.Add(new DataColumn("ShiftEndDateTime", typeof (DateTime)));
		
		table.Rows.Add("5/25/2019  10:20:29 PM","5/25/2019  10:59:36 PM");		
		table.Rows.Add("5/26/2019  9:43:24 PM","5/26/2019  9:43:14 PM");		
		table.Rows.Add("5/26/2019  9:43:24 PM","5/26/2019  11:19:41 PM");		
		table.Rows.Add("5/26/2019  9:43:24 PM","5/27/2019  4:22:14 AM");		
		table.Rows.Add("5/26/2019  9:43:24 PM","5/27/2019  4:23:29 AM");		
		table.Rows.Add("5/26/2019  9:43:24 PM","5/27/2019  5:32:42 AM");
		DateTime d1 = new DateTime(2019,05,26,21,43,24);
        DateTime d2 = new DateTime(2019, 05, 26, 5, 32, 42);
		DataRow[] rows = table.Select("ShiftStartDateTime >=#"+d1.ToString("yyyy/MM/dd hh:mm:ss tt")+"#"+"AND ShiftEndDateTime <=#"+d2.ToString("yyyy/MM/dd hh:mm:ss tt")+"#");
		foreach (var r in rows)
        {
        	//do something here.
        }
Posted
Updated 16-Jun-19 10:48am
v2
Comments
Gerry Schmitz 7-Jun-19 17:24pm
   
You "do something" first. And I guessed right.
BillWoodruff 7-Jun-19 18:22pm
   
Describe the error you have now: what is it ? where does it occur ?
Richard MacCutchan 8-Jun-19 4:08am
   
You have declared your columns as DateTime types, but you then try to add rows containing string values. Don't use strings for DateTime values, use only DateTime values. The only time you need to convert them to strings is when you want to display them in human readable form.
Member 14089908 8-Jun-19 13:33pm
   
I only have problem in the following code.
DataRow[] rows = table.Select("ShiftStartDateTime >=#"+d1.ToString("yyyy/MM/dd hh:mm:ss tt")+"#"+"AND ShiftEndDateTime <=#"+d2.ToString("yyyy/MM/dd hh:mm:ss tt")+"#");
Do you have any suggestion?
Richard MacCutchan 8-Jun-19 16:03pm
   
Yes, as I already suggested, stop using strings and use only DateTime types.
Member 14089908 9-Jun-19 8:05am
   
You means i can do like this?
DataRow[] rows = table.Select("ShiftStartDateTime >=#"+d1+"#"+"AND ShiftEndDateTime <=#"+d2+"#");
Thanks
Richard MacCutchan 9-Jun-19 8:20am
   
No. I repeat, do not use (or try to use) strings for comparing dates. Use proper DateTime objects, in your database, datatable and code.
Member 14089908 9-Jun-19 13:29pm
   
Hello Richard,
Could you please give the sample code.
Thanks
Richard MacCutchan 10-Jun-19 3:39am
   
Just change all your date strings to DateTime objects.

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Take a look at your code:
DateTime d1 = new DateTime(2019,05,26,21,43,24);
DateTime d2 = new DateTime(2019, 05, 26, 5, 32, 42);


d1 is bigger than d2, so... this condition: ShiftStartDateTime>=d1 AND ShiftStartDateTime <=d2 will never get true!

Try this:
DataRow[] rows = table.Select(string.Format("ShiftStartDateTime>=#{0}# AND ShiftEndDateTime<=#{1}#", d2, d1));
//returns:
//ShiftStartDateTime  ShiftEndDateTime
//2019-05-26 21:43:24 2019-05-26 21:43:14 
//
//Note, that my default culture is: PL-pl
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100