Prepare/Modify Datatable as Datetime column has null values

[Satish_Ch]

I have Datatable where column3 (FacilityTime) is Datetime. I want this column to be changed to “Object”. so that i can insert null values from FacilityTIme column to DB and also i have to format the datetime 10/12/2009 11:09:12 PM to required format while inserting into DB.

I am recieving below error if i dont change the column to object and change datettime format.
Could someone help me with this.

And its a continuation to my last questin Filter DataTable using Linq - #7 by Satish_Ch
@ppr @Raghavendraprasad @ClaytonM @supermanPunch @sarathi125 @Yoichi @arivu96
@PrankurJoshi @indra @poorna_nayak07 @Palaniyappan @Nithinkrishna @NIVED_NAMBIAR @Dave

[Rahul_Unnikrishnan]

Hello @Satish_Ch

Have you tried keeping the format of the column as Text? If not give it a try.

[Satish_Ch]

I tried nothing worked except changing column to object type.I did it using build datatable and for loop but now iam using linq as for loop taking too much time for 3000 rows.

[muhammedyuzuak]

Hi @Satish_Ch,

Can this field be datetime in your database? Can you check your table by coming to the design section?

If your db table is in date format, you will need to convert your latest data here. Also In the first datatable you build, you need to make the relevant column type datetime.

Regards,
MY

[Satish_Ch]

Yes it is Datetime only in Database. its accepting only particular format 12/12/1999 11:09:09 PM something like this, and i have null values also in my column . so i want to convert my datetime into object so that null values also can be stored and convert back the remaing values to datetime using datetime.parseexact methods.
SO i want to do this using linq as for loop is time taking.

[Satish_Ch]

I might require something like this.

Convert datatable strings column to datetime

@Angelo_Savino In General the LINQ Syntax should/could look like following (From row In Compressed2.AsEnumerable Let dt= DateTime.ParseExact(row.Item(“Column2”).ToString,“dd/MM/yy”,Globalization.CultureInfo.InvariantCulture) Select row).CopyToDataTable AsEnumerable is used for bringing the rows into an enumerable sequence There is no need to parseString into datetime (ParseExact Method) and then convert a datetime into a dateTime (Conver.toDate) But this statement will not do any changes.…

Can someone let me how to convert column to object type from datetime and after that convert values back to datetime.
@ppr @Yoichi

[THIRU_NANI (NaNi)]

Hey!

One small suggestion!

If you’re using build data table we have an option called allow null values. when we check this option this will allow us to add the null values also…

Convert the string to datetime try like this

Cdate(Input).ToString("dd-MM-yyyy hh:mm:tt")

Regards,
NaNi

[Satish_Ch]

i already did with build datatable and i am able to find the solution as well but as its taking i want to do it using linq.

[supermanPunch (Arpan)]

Hi @Satish_Ch ,

Could you let us know How are you reading the TrailerData datatable?

Is it being read by Read Range Activity ? If that is the case, then the columns should normally be of Object type.

You can use the Query below to check the Datatype of the Columns in Debug Panel.

DT.Columns.Cast(Of DataColumn).Select(Function(x)x.DataType.ToString).ToArray

[Satish_Ch]

Okay I am using read range only.So its object type.Then i just have to convert my column values to datetime desired format.
May i know how to do it using linq.

[supermanPunch (Arpan)]

@Satish_Ch ,

Could you try using the below Snippet component and check if you are able to achieve it ?

Novigo Solutions - Change Column Type to DateTime - RPA Component | UiPath...

Here the Solution is to change the Column Data type so that the Database Table accepts the Datatable.

It cannot be only achieved with Linq alone, we would need to change the Data column type of the Date column itself.

Let us know if the component also takes a long time, we’ll suggest other solutions.

Therer are other Components in marketplace as well :

Easy DataTable Activities - RPA Component | UiPath Marketplace

Let us know if you were able to find the solution using any of them.

[Satish_Ch]

I cant use components in marketplace.

[supermanPunch (Arpan)]

@Satish_Ch ,

Could you check the below workflow :
ConvertColumnTypeToDate.zip (15.8 KB)

Let me know if it gives out an error or it takes a longer time for completing the execution.

[Dave (David Wiebelhaus)]

@Satish_Ch Datetime doesn’t allow null values in vb.net - You will have to create type nullable(of datetime) instead for the column. Then pass Nothing for the null values

[Satish_Ch]

Already done the same.Anyway issue resolved now thanks for responses.