I’m trying to filter datatable based on dates, when I tried with (out_dt=[Date] >= ‘07/15/2019’ and [Date] <= ‘07/31/2019’) it works fine, but I need to get filter data by only giving starting date i.e., 15 and end date, month, year should not be hardcoded.
is it possible to filter data by giving dates dynamically?
Can anyone help me in this concern?
You can use variables to hold the date you want. Then you can use the variable in the Filter Data Table activity to filter based on the values you need without hard coding…
Hi
Hope this would help you
—fine we can use select method like this
Yourdatatable = Yourdatatable.Select(“[Datecolumnname] >= ‘07/15/2019’ AND [Datecolumnname] <= # “ + DateTime.DaysInMonth(Now.ToString(“yyyy”),Now.ToString(“MM”)).ToString(“MM/dd/yyyy”) + “ # “ )
Here DateTime.DaysInMonth(Now.ToString(“yyyy”),Now.ToString(“MM”)).ToString(“MM/dd/yyyy”) gives the last day of the month so we don’t need to hard code that
Kindly try this and let know for any queries or clarification
Cheers @farook2
Hi @Palaniyappan Thanks for your response,
Instead of giving 07/15/2019, I need to give only starting date i.e.,15. Is there any way to mention only starting date?
Yeah this is what im worried about, I tried above method again im getting errors,
Can you create a workflow on this? I have attached the sample excel sheet.sample excel.xlsx (8.5 KB)
I am using an invoke code activity, so you’r not limited by the studio version or any depencies
A simple code guide:
You are using an assign for the beginDate and endDate to set them as you wish
In the code, I am simply comparing the day to determine the filtering and I’m adding the rows into another table, which in the end will become the inputTable
To elimitate eny errors, if your input date is bigger than the current month max day (ex: endDate = 30 and the month is february) the code will change for that month the endDate to the max days number in the current month from the row, no the system current month
Dim dateFromRow As DateTime
Dim lastDayInMonth As Integer
Dim filteredTable As DataTable = table.clone()
If beginDate = 0 Then
beginDate =1
End If
For Each inputRow As DataRow In table.Rows
Console.WriteLine(inputRow(0).ToString)
dateFromRow = DateTime.ParseExact(inputRow(0).ToString,"MM/dd/yyyy hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture)
lastDayInMonth =DateTime.DaysInMonth(dateFromRow.Year,dateFromRow.Month)
Console.WriteLine(lastDayInMonth.ToString)
If endDate > lastDayInMonth Then
endDate = lastDayInMonth
End If
Console.WriteLine("Date: "+endDate.ToString)
If (dateFromRow.Day >= beginDate And dateFromRow.Day <= endDate) Then
filteredTable.ImportRow(inputRow)
End If
Next
Console.WriteLine(“done filtering”)
table= filteredTable
I’m inserting a picture with the invoke code arguments:
As for the first activity that is missing is a multipleAssign
Below are the project local variables:
In the first activity missing is a multiple assign to set the begin and end date
I want to filter my data table according to the current month that is going on.
like if its april i should just filter out april data,if its may then only may data and so on can anyone help.