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
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
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
@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
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:
Use an integer variable to capture the ordinal of the old column. assign OldColumnIndex = MyTable.columns("oldColumnName").Ordinal
Create new column - this can be done with a ‘add data column’ activity. Make sure the datatype is set to be system.datetime
Use a for each row activity with a single assign within it: Assign row.item("NewColumnName") = cdate(row.item("oldColumn"))
Use a ‘remove data column’ activity to delete the old column
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)
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)
parvathi_ayanala
jamiejam
Anirudh_Tugawe