Datatable convert object to date time field

i have a csv which i am importing to a datatable

Status,Reference,Started
Successful,Task1,9/20/2021 11:49:34 AM,
Successful,Task1,9/10/2021 11:51:12 AM,
Successful,Task2,9/15/2021 11:52:51 AM,
Successful,Task3,9/12/2021 11:53:53 AM,
Fail,Task 1,9/01/2021 11:55:07 AM,
Successful,Task 2,9/20/2021 12:00:53 PM,
Successful,Task 3,8/20/2021 12:02:47 PM,
Successful,Task 1,8/29/2021 12:03:32 PM,
Successful,Task 2,7/01/2021 12:04:46 PM,
Fail,Task 3,7/06/2021 12:18:31 PM,
Successful,Task 1,4/24/2021 12:18:36 PM,
Successful,Task 2,9/14/2021 12:18:43 PM,
Successful,Task 3,6/24-2022 12:36:40 PM,

what i am trying to do is get the started field into a format where i can then filter it in a datatable to return the rows where the the started date is greater than 11th of Sept. the current format is MM/dd/yyy hh:mm:ss am(or pm)

i can either use the started field as it is or add a new row - open to any suggestions

thanks

Read CSV into datatable. Add datetime column to datatable. For Each Row in Datatable, and Assign the new column’s value to the string date with DateTime.Parse

Hi @adrian_sullivan,
This post contains other methods that you could use for converting all values of a column to a date time type - Convert datatable strings column to datetime

But these essentially are doing the same thing that @postwick has mentioned - parse specific column of the row in each iteration while looping through the datatable

Let me know if this helps

Thanks,
Nishant

i added a new row - NewDate with a data type system.datetime

image

then i added an assign in a for each row
row.Item(“NewDate”) = DateTime.ParseExact(row.item(“Started”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)

and i get this error - string was not recognised as a valid datetime

Hi @adrian_sullivan ,

Firstly, Could you check Instead of using the DateTime.ParseExact() use CDate(). If it still gives out an error / not intended result. Check the Date values in the datatable using Debug. We need to understand the format/formats the date values produce in the Datatable. Then we use those formats in the DateTime.ParseExact().

Follow the below post for Analysis on Date values in Datatables :

That’s because you are using ParseExact
Try using DateTime.Parse(string_var), that should work for you

Thanks,
Nishant

Your date format is wrong. Try this:

DateTime.ParseExact(row.item(“Started”).ToString,“MM/dd/yyyy hh:mm:ss tt”,Globalization.CultureInfo.InvariantCulture)

The format has to match your date input string’s format which is 9/14/2021 12:18:43 PM

ParseExact works fine. He just provided an incorrect format.

That’s right! Nothing wrong with ParseExact.

I suggested Parse since this would be a quick-fix and would encompass any changes (if any) in future

Regards,
Nishant

still getting an error

@adrian_sullivan ,

Have you tried to use the CDate() method and checked if it is working ?

CDate(row("Started").ToString)

If it does not work, You could check my above post or directly linking the Section of the post on allowing different date time format specifications is provided below :

CDate(row(“Started”).ToString) outputs the date as 06/24/2022 12:36:40 - which is ok

but…

when i try an run a filter on it for records after a specified time eg 06/23/2022 there is nothing returned. i have tried a few different date options but none will work

image

@adrian_sullivan .

Unless the NewDate column is a DateTime type of data column we will get inaccurate results with Filter Datatable activity.

For such filter cases, we use Linq approach for a less step execution work around.

In your case, the Linq query would be like below :

DTData = DTData.AsEnumerable.Where(Function(x)x("Status").ToString.Equals("Successful") andAlso CDate(x("NewDate").ToString).Date >= (new DateTime(2022,6,23)).Date).CopyToDatatable

Let us know if you are still getting an error.

Also, we ask you to check the post above on Debugging the Date values to understand if there are truly different date format values found. This will help us clear most of our confusions and help to deliver you the solution required.

thanks for taking the time to help with this

that is working for me now - one last question \ tweek
i dont need the filter for Successful, i tried to remove it but the query keeps breaking.
also if i wanted to set a max date as well would it look something like this andAlso CDate(x(“NewDate”).ToString).Date <= (new DateTime(2022,9,25)).Date)

thanks

@adrian_sullivan ,

Could you provide more details on what exactly do you mean by it keeps breaking ? Is It a validation error or Runtime error ?

Also, Could you provide the full expression used ?

this is what i am trying to run
DTData.AsEnumerable.Where(Function(x)x CDate(x(“NewDate”).ToString).Date >= (new DateTime(2022,9,01)).Date andAlso CDate(x(“NewDate”).ToString).Date <= (new DateTime(2022,9,20)).Date).CopyToDatatable

in effecct >1st of Spt and less than 20th of Sept

the error i am getting is a validation error look like this

image

@adrian_sullivan ,

There is a floating x in the query, it should be removed.

DTData.AsEnumerable.Where(Function(x) CDate(x("NewDate").ToString).Date >= (new DateTime(2022,9,01)).Date andAlso CDate(x("NewDate").ToString).Date <= (new DateTime(2022,9,20)).Date).CopyToDatatable

You’re comparing to the date as a string. This won’t work.

Hello, @adrian_sullivan - Can you please share sample input excel

that is working for me now

thanks for all the help folks

1 Like