Datatable convert object to date time field

[adrian_sullivan]

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

[postwick (Paul)]

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

[Nishant_Banka1 (Nishant Banka)]

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

[adrian_sullivan]

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

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

[supermanPunch (Arpan)]

@adrian_sullivan

DateTime.ParseExact(row.item(“Started”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture)

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 :

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", Culture…

[Nishant_Banka1 (Nishant Banka)]

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

Thanks,
Nishant

[postwick (Paul)]

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

[postwick (Paul)]

ParseExact works fine. He just provided an incorrect format.

[Nishant_Banka1 (Nishant Banka)]

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

[adrian_sullivan]

@postwick

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

still getting an error

[supermanPunch (Arpan)]

@adrian_sullivan

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 :

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", Culture…

[adrian_sullivan]

@supermanPunch

CDate(row("Started").ToString)

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

[supermanPunch (Arpan)]

@adrian_sullivan

@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.

[adrian_sullivan]

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

[supermanPunch (Arpan)]

@adrian_sullivan

i tried to remove it but the query keeps breaking.

@adrian_sullivan

like this andAlso CDate(x(“NewDate”).ToString).Date <= (new DateTime(2022,9,25)).Date)

@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 ?

[adrian_sullivan]

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

[supermanPunch (Arpan)]

@adrian_sullivan

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

@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

[postwick (Paul)]

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

[ushu (Usha kiranmai)]

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

[adrian_sullivan]

that is working for me now

thanks for all the help folks