How to filter data table by dateandtime

Help Studio
,
1

Hi Team,

How to filter the data table using DateTime.

For example

14-11-2022 14:21
14-11-2022 14:26
14-11-2022 14:54
14-11-2022 18:20
14-11-2022 18:44
14-11-2022 18:44
14-11-2022 18:47
14-11-2022 18:50
14-11-2022 18:58
14-11-2022 18:59
14-11-2022 19:07
14-11-2022 19:09
14-11-2022 19:22
14-11-2022 20:37
14-11-2022 20:44
14-11-2022 20:48
14-11-2022 23:48
13-11-2022 10:48
13-11-2022 23:48

As per the above input in the data table, I need the date as per the below conditions

Bot to process the transaction based on the date & time stamp (T-2 after 23:00:00 until 23:59:59 & T-1 from 00:00:00 until 22:59:59)

2

Hi @pra.vemunuri

For filtering use this. Say dt is your datatable

Dt.AsEnumerable.Where(function(row) datetime.diff(dateinterval.hours,datetime.Paeraeexact(row(“datecolumn”).tostring,”dd-MM-yyyy hh:mm”,system.Globalization.culturalinfo.invariantculture),”date that you want to compare”).copytodatatable

This will give you the filtered dt… but i did not understand what is T and T-1 and 2…so accordingly give the date in compare. Else if you could explain i can help

Cheers

3

Would this throw an exception if one of the row items for the datecolumn are blank or not in correct format?

4

Hi @grant.grisham

If the dateformat is wrong or empty string then yes this will throw an error …you can continue or ignore that error using try catch and continue options

cheers

5

filter the datatable of invalid or empty strings beforehand and avoid it all together.

6

HI,

Can you try the following sample?

image
image903×645 32.1 KB 

dt.AsEnumerable.Where(Function(r) DateTime.TryParseExact(r("datetime").ToString,"d-M-yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,New DateTime) AndAlso DateTime.ParseExact(r("datetime").ToString,"d-M-yyyy HH:mm",System.Globalization.CultureInfo.InvariantCulture).TimeOfDay<New TimeSpan(23,0,0)).CopyToDataTable()

OR

dt.AsEnumerable.Where(Function(r) DateTime.TryParse(r("datetime").ToString,New DateTime) AndAlso DateTime.Parse(r("datetime").ToString).TimeOfDay<New TimeSpan(23,0,0)).CopyToDataTable()

Sample20221116-2.zip (2.9 KB)

Regards,

7

Hi @Yoichi Thanks for your help.

How to filter based on Date and time.

T-2 after 23:00:00 until 23:59:59 & T-1 from 00:00:00 until 22:59:59)

T-2 Means for example Tody’s date is 14 then T-2 = 13th(T-2 after 23:00:00 until 23:59:59)

T- 1 means for example Tody’s date is 14 then T-1=14th(T-1 from 00:00:00 until 22:59:59))

9

I have written in C# however it’s not working while using the Invoke code

var dt = dt1.AsEnumerable().Where(p => (Convert.ToDateTime(p[“Date”]) >= Convert.ToDateTime(“13-11-2022 23:00:00”)) && (Convert.ToDateTime(p[“Date”]) <= Convert.ToDateTime(“13-11-2022 23:59:59”)) || (Convert.ToDateTime(p[“Date”]) >= Convert.ToDateTime(“14-11-2022 00:00:00”)) && (Convert.ToDateTime(p[“Date”]) <= Convert.ToDateTime(“14-11-2022 22:59:59”))).CopyToDataTable();

10

Hi,

Did you have any error message? Or was the result not your expected?

Regards,

11

no error mate

i have to filter with date and time

T-2 after 23:00:00 until 23:59:59 & T-1 from 00:00:00 until 22:59:59)

T-2 Means for example Tody’s date is 14 then T-2 = 13th(T-2 after 23:00:00 until 23:59:59)

T- 1 means for example Tody’s date is 14 then T-1=14th(T-1 from 00:00:00 until 22:59:59))