Date condition

Help
1

Hi,
I am using CSV file as input and writing to Excel file.
how to filter the records from the datable based on the date condition.My format of date in my file is MM/DD/YYYY
I am using the condition as follows: bookdata.Select(“Published_Date >= #11/30/2017#” ).
But it is picking up all the records.Can anyone help on this?

below is the data from my CSV file.

Author_name Book_name Price Published_Date
Vikas Tamil book 25 10/25/2017
cavendish Maths book 50 11/1/2017
SAP Science book 23 11/12/2017
Pearson Malay book 19 12/5/2017
Marshal History book 30 9/1/2017
Peason Science book 40 9/10/2017

2

which activity you used to write the data to excel? workbook write range ? or excel application scope?

3

Hi,
I am using Excel application scope and inside I use write range activity.The problem is the filter does not work correctly as expected.It picks up all the records from my csv file and write it to excel file.Is my sytax correct in the Select method?

4

Try this. bookdata.Select(“Published_Date >= '#11/30/2017#' ” )

5

you can try this

dt.asEnumerable().Where(Function(r) Date.ParseExact(r("Published_Date").tostring,
{"MM/dd/yyyy","M/dd/yyyy","M/dd/yyyy","M/d/yyyy"},Nothing,DateTimeStyles.None) >= `Date.Parse("11/05/2017") ).copytoDataTable
6

this doesn’t work.still picks all records.

7

have you tried this ?

8

yes.but I get compiler error saying DatasTimeStyles is not declared.it may be inaccessible due to its protection level.

9

go to Imports and import System.Globalization

10

Actually I am using the select method and assigning it to datavariable of datarow type in Assign activity.
if I use the above code,it gives me the compiler error "Cannot convert system.data.datatable to system.data.datarow in Assign activity

11 
dt.asEnumerable().Where(Function(r) Date.ParseExact(r("Published_Date").tostring,{"MM/dd/yyyy","M/dd/yyyy","M/dd/yyyy","M/d/yyyy"},Nothing,DateTimeStyles.None) >= Date.Parse("11/05/2017") ).toArray
12

can I use this in the Assign activity?

13

yes, assign to array of DataRows

14

thanks.now it is picking up the filtered records.
How to modify this code to select records between two dates.?

15

is there any other simple way to get the date based on the date condition?

16

Select won’t work because in datatable every columns are considered as Object Data Type.
Try this, It May work,

dt.Select(“Convert(Published_Date,System.DateTime) >= '#11/30/2017#' ”)
17

Thanks.it is working fine and I followed the same code to add between twp dates and that also works fine.Thanks for your help and immediate response.

1 Like