Filter data table for previous month

Help
#1

Hello,

I need to read excel data into data table but only for the prev month. The column is a date data type and in the following format - M/d/yyyy. For example, column can have dates like -
6/21/2018
6/14/2020
9/18/2017
11/16/2020

But I need to filter the column for 11/d/2020

#2

Hi @meghna.b.das,
You filter using various methods, I will provide one way of filter below find the below query.

Dt.Select("columnname like '11/%'").CopytoDataTable()

Regards,
Arivu

2 Likes
#3

how can I make the month dynamic in this query?

#4

please use below query

Dt.Select("columnname like '"+Now.AddMonths(-1).Month.ToString()+"/%'").CopytoDataTable()

Regards,
Arivu

#5

Thanks. My column is called System Book so my query looks like this -

dt1prevmonth = dt1.Select(“System Book like '”+Now.AddMonths(-1).Month.ToString()+"/%’").CopytoDataTable()

However, I am getting an erroe like this - Assign: Syntax error - Missing operand after ‘Book’ operator.

#6

@meghna.b.das Since you have Space in the Column Name you might need to put the Column Name inside a Bracket in this way [Column Name]

#7

Hi!

TargetMonth = Now.AddMonths(-1).Month

NewDT = dt.AsEnumerable.Where(Function (row) DateTime.ParseExact(row(“date”).ToString,“MM/dd/yyyy hh:mm:ss”, Nothing).Month.Equals(TargetMonth)).CopyToDataTable
or maybe
NewDT = dt.AsEnumerable.Where(Function (row) DateTime.ParseExact(row(“date”).ToString,“MM/dd/yyyy”, Nothing).Month.Equals(TargetMonth)).CopyToDataTable

depends on your input

#8

I used this-
dt1prevmonth=dt1.AsEnumerable.Where(Function (row) DateTime.ParseExact(row(“System Booking Date”).ToString,“MM/dd/yyyy”, Nothing).Month.Equals(TargetMonth)).CopyToDataTable

But am getting the following error -
Assign: String was not recognized as a valid DateTime.

#9

Yes, that’s why I wrote

NewDT = dt.AsEnumerable.Where(Function (row) DateTime.ParseExact(row(“date”).ToString,“MM/dd/yyyy hh:mm:ss”, Nothing).Month.Equals(TargetMonth)).CopyToDataTable

#10

The date in the column is in this format though - MM/dd/yyyy

#11

That’s because of excel formatting. I have the same feature.

When UiPath reads excel it may convert MM/dd/yyyy to MM/dd/yyyy hh:mm:ss automatically

#12

I tried with hh:mm:ss. it didn’t work

#13

Than you should just apply ParseExact arguments to your date string format.

How to get valid date string format? You can see it just with a log message activity
image

In my case it is MM/dd/yyyy hh:mm:ss
What is yours?