How to Update Date format in whole column of Datatable

Hi,

I want to convert “7 September 2020” Date format to “dd-MM-yyyy” date format for whole column in the Datatable. I am getting “String was not recognized as a valid DateTime” error. I have attached my Dummy.xaml file. Kindly provide me solution.

Used Expression : Date.ParseExact(""+str+"",“dd MMMM yyyy”, System.Globalization.DateTimeFormatInfo.InvariantInfo).ToString(“dd-MM-yyyy”)

Tried expression : (From row In ExtractDataTable Order By DateTime.ParseExact(row.Item(“Date”).ToString, “dd-MM-yyyy”,
System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable

Dummy.xaml (8.2 KB)

@Stalin_R

Check as below
https://forum.uipath.com/t/convert-date-time-format-tutorial/242670/2

Hope this helps you

Thanks

I tried your suggestion. Still I am getting error. My date format is like 7 September 2020 without any hyphen or slash. So we have try some othe rmethod

@Stalin_R

convert.ToDateTime(str).ToString("dd-MM-yyyy")

Missed double quotes

Thanks

If possible can you rework on my attached workflow and help me to find solution. That will be great if you could help.Dummy.xaml (11.7 KB)

Hi @ksrinu070184 ,

Thanks Sir, I found solution. This expression is working fine. convert.ToDateTime(str).ToString(“dd-MM-yyyy”)



1 Like

@Stalin_R

Great!!

Mark as solution if this helps

Thanks

Hi,

I have one more doubt. I was trying to filter date column with condition like I want all the rows from datatable if it has more than or equal to today’s date in the date column. I tried below expression. But I was getting “The source containing no data” error. Even though it’s not returning any rows, I want to append this datatable result in Excel. I tried filter datatable , But I am not sure how to filter with today’s date

ExtractDataTable.Select(“ReviewedDate >=’”+DateStr+"’").CopyToDataTable

@Stalin_R You aren’t able to do that because you are storing it as a string, not a date. If you need to use datetime functions (such as comparing one date to another) then it is recommended to store it as a date column rather than a string column. Then when you need to use it as a string anywhere you can just use the .ToString(“YourFormat”) to output as a string in the format you prefer.

If you really do need to keep it as a string for some reason, then you’ll have to alter your statement. Instead use linq/lambda which you can use to convert each string in the column to date. It’s really much easier to just convert the whole column to datetime though

Hi Dave,

Thanks for your response. Can you provide me expression that how to convert particular string column to Date time format in datatable using assign activity. After converting it, It’s easy to provide condition for or compare two dates in Filter data table right?

Yes converting it to date makes it much easier to compare everything. It should work fine comparing two dates using the ‘filter data table’ activity, although I haven’t used that activity before so I can’t say with 100% certainty.

It will take a few steps to convert it, not just 1 assign activity. The steps are:

  1. Use an integer variable to capture the ordinal of the old column. assign OldColumnIndex = MyTable.columns("oldColumnName").Ordinal
  2. Create new column - this can be done with a ‘add data column’ activity. Make sure the datatype is set to be system.datetime
  3. Use a for each row activity with a single assign within it: Assign row.item("NewColumnName") = cdate(row.item("oldColumn"))
  4. Use a ‘remove data column’ activity to delete the old column
  5. Use invoke method to set the ordinal of the newly created column to be the same as the old column.
    TargetObject: MyTable.Columns(“NewColumnName”)
    Method: SetOrdinal
    Paramaters (in) (of type: integer): value = OldColumnIndex (this is the integer variable from step 1)

HI Dave,

I tried your steps and finding some error when I am converting date from string column to newly created name column. Kindly help me on this

image

It looks like it is in either "dd-MM-yyyy format, or in “d-MM-yyyy” format. I think cdate uses US format where the day/month is opposite. Instead of cdate you can use DateTime.ParseExact() so it would look like this: DateTime.ParseExact(row.item("Date").ToString,{"dd-MM-yyyy","d-MM-yyyy"},CultureInfo.InvariantCulture,DateTimeStyles.None)

Thanks for your beautiful response. It worked for me. Thanks @ksrinu070184 and @Dave . Marking this topic as “Solved”

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.