Linq Query to Filter future and past date

[sharu_priya]

Hello,

I want to filter the previous and future date from date of completion column.

How to achieve this using Linq Filter

Regards,
Sharu

[Anjani_Kapoor (Anjani Kumar)]

Hi @sharu_priya ,

If you want separate table for past and future table. Use assign activity as follow
dt_PastDates = Table.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) < DateTime.Today.Date).CopyToDataTable

dt_FutureDates = Table.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) > DateTime.Today.Date).CopyToDataTable

In case your requirement is such that you want all the records except today’s. which means past and future in one single table, use the below code:

dt_NotTodayDates = Table.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) <> DateTime.Today.Date).CopyToDataTable

Thanks!!

[sharu_priya]

@Anjani_Kapoor

Table.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) > DateTime.Today.Date).CopyToDataTable

Thank you so much…

What should be the datatype of dtpast and dtfuture

[Anjani_Kapoor (Anjani Kumar)]

it should be dataTable. also in “Value to Save” field you only need to write the query and not table reference as dt_pastDates.
thus, in “Save to” type in dt_pastDates and in
“Value to Save” - DT1.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) < DateTime.Today.Date).CopyToDataTable

[sharu_priya]

Thank you…

But getting the below error

[sharu_priya]

I resolved that… It was because i just copy pasted … so i just removed the double quote and typed again. It got worked.

Thank you so much

[Anjani_Kapoor (Anjani Kumar)]

@Anjani_Kapoor

DT1.AsEnumerable.Where(function(r) cdate(r(“Date of Completion”)) < DateTime.Today.Date).CopyToDataTable

Great !! thank you for confirming

[sharu_priya]

But why am i getting the output as below…

I need only date of completion column alone

is it because you are giving copy to datatable at the end

[ashokkarale]

@sharu_priya,

Use this workflow:

Added this logic to keep only the required column in the datatable.

dtfutureDates.DefaultView.ToTable(False,"Date of Completion")

Sequence.xaml (12.1 KB)