Selecting a row from excel from a particular ramge of date

Help
, ,
1

Hi Everyone,

I have an excel with column name as Transaction date and various other column as well i have to extract all the rows from this excel whose date is between 15 and 31.
I have used this code HdfcSelcolumns.Select(“Transaction Date LIKE '%/”+Month+“/”+Year+"’ ").CopyToDatatable
Here HdfcSelColumns is a datatable and Transaction Date is a column in my excel and month and year is a string with month and year number.
I am getting an error saying Syntax error: Missing operand after ‘Date’ operator.

2

@aamir

try this query

(From p in dt.Select()
where convert.ToInt32((Convert.ToDateTime(p.Item(“Transaction Date”))).Day)>15 andconvert.ToInt32((Convert.ToDateTime(p.Item(“Transaction Date”))).Day)<31
select p).ToArray.CopyToDataTable

dt - your datatable variable

Regards,
Mahesh

3

Hi Mahesh,

getting error

image
image1366×768 110 KB

4

Hi,

The above screenshot is wrong i have removed dt and kept my datatable. Still getting an error

image
image1366×768 110 KB

5

And i forgot to mention i want date 15 and 31 also to be included in it

6

Hi Mahesh,

The above error is removed now however i am getting the below error now after running the query
Assign : String was not recognized as a valid DateTime.

7

Hi mahesh,

Any update as why that error?

8

Try This one @aamir
(From p in dt.Select() where convert.ToInt32((Convert.ToDateTime(p.Item("Transaction Date"))).Day)>=15 and convert.ToInt32((Convert.ToDateTime(p.Item("Transaction Date"))).Day)<=31 select p).ToArray.CopyToDataTable()

9

@aamir

Is your Excel sheet date is in this format, dd/MM/yyyy or dd-MM-yyyy

Else
if the transaction date is not in the above format try this,

(From p in dt.Select()

where convert.ToInt32(DateTime.ParseExact((p.Item(“Transaction Date”)).ToString,stringformat,System.Globalization.CultureInfo.CurrentCulture).Day)>15 and convert.ToInt32(DateTime.ParseExact((p.Item(“Transaction Date”)).ToString,stringformat,System.Globalization.CultureInfo.CurrentCulture).Day)<31

Select p).ToArray.CopyToDataTable()

Sry If it throws any syntax error because i have not tested this code…

Regards,
Mahesh

1 Like
10

Hi Mahesh,

My date format is dd-mm-yyyy

11

Hi Mahesh,

Got the following error

image
image1366×768 113 KB

12

@aamir

You have to give “dd-MM-yyyy” in string format place

Regards,
Mahesh

13

Hi Mahesh,

Got the below error using the code below
(From p In HdfcSelcolumns.Select()
Where convert.ToInt32(DateTime.ParseExact((p.Item(“Transaction Date”)).ToString, “dd-MM-yyyy”,System.Globalization.CultureInfo.CurrentCulture).Day)>15 And convert.ToInt32(DateTime.ParseExact((p.Item(“Transaction Date”)).ToString, “dd-MM-yyyy”,System.Globalization.CultureInfo.CurrentCulture).Day)<31
Select p).ToArray.CopyToDataTable()
Assign : String was not recognized as a valid DateTime.

14

@aamir

is this “dd-MM-yyyy” the only format you have in your excel sheet

Regards,
Mahesh

15

I have this only format there is some time also associated with that which i am removing after ward let me remove that time at the initial stage.

16

Hi Mahesh,

i removed the time also now first only left with date still getting the same error should i attach my workflow with you.

17

@aamir

try with some rows to test by giving the date format as dd-MM-yyyy. then check whether it is giving the same error or not.

Your Datacolumn should have only this format “dd-MM-yyyy” I think it will work

Regards,
Mahesh

18

Shall i attach you my excel and code.

19

@aamir

That error is throwing because your datacolumn contains other format also…so just try once by creating one seperate excel sheet having a single date format…Even at that time also if it throws exception then you share your excel and xaml.

Regards,
Mahesh

20

Sure