Filter only current month rows [column name :Expiration Date] in given data table
need any select or Linq to filter Expiration Date column to find out this month data.
Hi,
Can you try the following expression?
dt = dt.AsEnumerable.Where(Function(r) DateTime.Parse(r("Expiration Date").ToString).Month = Today.Month AndAlso DateTime.Parse(r("Expiration Date").ToString).Year = Today.Year ).CopyToDataTable
Regards,
Can you try to use the Filter Data table (Refer to the screenshot)
Regards
Gokul
no luck. returned unexpected values
String was not recognized … exception does mean that expected format / data is different
give a first try on trimming the date values by
DateTime.Parse(r(“Expiration Date”).ToString.trim()).Month
do it also for the Year part of Yoichi’s approach
in case of it will fail again we can check more in detail the failing dates within a second anlysis round
@prabhu_ponnusamy - Is it possible to share sample data after masking/removing sensitive information?
Hi @prabhu_ponnusamy ,
If ppr’s solution still doesn’t work, there might be blank cell and/or illegal characters as datetime string. The following will filter out those.
dt = dt.AsEnumerable.Where(Function(r) DateTime.TryParse(r("Expiration Date").ToString.Trim,New DateTime()) AndAlso DateTime.Parse(r("Expiration Date").ToString.Trim).Month = Today.Month AndAlso DateTime.Parse(r("Expiration Date").ToString.Trim).Year = Today.Year ).CopyToDataTable
Note: Perhaps you should check if this doesn’t filter out rows which you want to keep.
Regards,
1 Like
Test.xlsx (25.9 KB)
Attached is the correct excel where we do apply this rule
Expiration Date has rows with empty values along with a space. This is the reason for the invalid format expception
It is now to define on how to handle this
- use a default date
- do cleansing on a copy of the datatable and do afterwards the filtering (give a check on this)
ashokkarale
prashant1603765
oscar
s.elamriti
Roman-Routinuum
muthuraman.g
kristopherk
pikorpa