Search in datatable with datetime in C#

Question

0.00/5 (No votes)

See more:

, +

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 7-Jun-19 8:27am

Member 14089908

Updated 16-Jun-19 10:48am

v2

Add a Solution

Comments

[no name] 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

Add a Solution

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

Maciej Los · Answer 1 · 2019-06-16T10:48:00

Take a look at your code:

C#

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:

C#

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

Search in datatable with datetime in C#

1 solution

Solution 1

Add your solution here

Preview 0

Search in datatable with datetime in C#

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us