Change date format in column

[jntrk]

Hey everyone I have a column in a datatable where the format of date is d.MM.yyyy , I want to change it all values in this column to MM/d/yyyy and I cannot use for each activity because there are to many rows to take this action for each row. I might change the “.” with “/” using .Replace but I have no idea how to change the position of day and month.

I appreciate every bit of help, thank you

[abu.behlim (Abu Hurera Behlim)]

Hi @jntrk
Use the below method
Date.ParseExact(DT.Rows.item(“ColumnName”),“d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”)

[1996 (Neelima G)]

Hi @jntrk,

Try this below code

Cdate(yourvariable.ToString).ToString(“MM/d/yyyy”)

Thanks!

[jntrk]

Sorry for the late response, I encountered with this issue,

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

U can try this to change the date format to MM/d/yyyy

Datatable_variable.AsEnunerable().ToList().ForEach(Sub(row) row(columnName)= Date.ParseExact(row(“ColumnName”),“d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

U can try this in a invoke code activitiy.

But when u write the datatable in excel it is better to add the data in text format rather than adding in datetime because of excel cell may be formated in one date format, like they may be formated in dateformat of dd.MM.yyyy but even when u paste the date in format of MM/dd/yyyy it will appear as dd.MM.yyyy.

Before writing the datatable to excel try to convert the date format in excel to.required one either manually or using Balareva activitiy.

Hope the information helps you

Regards

Nived N

Happy Automation

[jntrk]

@NIVED_NAMBIAR

Datatable_variable.AsEnunerable().ToList().ForEach(Sub(row) row(columnName)= Date.ParseExact(row(“ColumnName”),“d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

Hey NIVED,

Can you send me a xaml, of this I’ve worked on it but cannot resolve the errors.

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

it seems i had done some mistakes from my side in the code
kindly try with this code
Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row(columnName)= DateTime.ParseExact(row(“ColumnName”).ToString, “d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

Regards,
Nived N
Happy Automation

[jntrk]

Thank you for your fast response, I get the following.

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

Check this

AsEnumerable is not a member of 'System.Data.Datatable'

When trying to use the AsEnumerable() method on a data table, i receive a error stating that AsEnumerable is not a member of ‘System.Data.Datatable’. I’ve seen someone else with this problem and their solution was to use temporary data table activities in their project to load in the System.Data.DataSetExtension library. However this has not worked for me. Any help would be greatly appreciated. Thank you.

[jntrk]

" -<System.Data.DataSetExtensions> - " is actually added in my main file but still the same issue.

[NIVED_NAMBIAR (NIVED N)]

Hi @jntrk

Just see the response I had edited it

[jntrk]

@NIVED_NAMBIAR

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row(columnName)= DateTime.ParseExact(row(“ColumnName”).ToString, “d.MM.yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“MM/d/yyyy”))

yess finally I got rid of that problem, now I get this

Invoke code: Exception has been thrown by the target of an invocation.
Sorry for the troubles I am causing.

[jntrk]

now I went over the datatable read range takes cell as “02/22/2020 00:00:00” etc. I want to change it to 22.02.2020,

currently the code is like this:

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row("DDate")= DateTime.ParseExact(row("DDate").ToString, "MM/dd/YYYY hh:mm:ss",System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy"))

do you see any mistake?

[NIVED_NAMBIAR (NIVED N)]

did u change the did u put the columnname u need to change the date format in the invoke code

[instead of “columnname” that i had provided ]

also tick the preserve format option

[jntrk]

I enabled preserve filter:

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row("DDate")= DateTime.ParseExact(row("DDate").ToString, "MM/dd/YYYY",System.Globalization.CultureInfo.InvariantCulture).ToString("dd.MM.yyyy"))

its like this, the column is named “ddate” i switch it with your columnname variable as a string, is the one I wrote here is wrong?

[prasath17 (Guru)]

@jntrk - Could you please show/share your input file screenshot??

[jntrk]

[prasath17 (Guru)]

@jntrk - Please try like this…

Datatable_variable.AsEnumerable().ToList().ForEach(Sub(row) row(“DDate”)= DateTime.ParseExact(row(“DDate”).ToString, “M/d/yyyy”,System.Globalization.CultureInfo.InvariantCulture).ToString(“dd.MM.yyyy”))

Because when you have single digit months and dates it was written without prefixing 0…

DateTime Format In C#

[jntrk]

yes wheen I step into debug, when I check the value inside the datatable_variable its 2/22/2020, I still get the same error tho

Invoke code: Exception has been thrown by the target of an invocation.

[prasath17 (Guru)]

@jntrk - Could you please empty other columns and send/share the sheet with only date value on it??