I am trying to sort (DESC) a datatable by a column, which comes in date format (dd-mm-yyyy); but the sort activity is taking it as a string. Any idea of how I might be able to accomplish this?
Many thanks for any input you might be able to provide with.
Just like @PrankurJoshi mentioned You need to parse the date to a date time format before you can sort. Since this is a datatable, you can follow the below steps too.
add a new column to your datatable which is of date time format to hold the converted date
use a for each row activity to loop through the datatable
within the loop, use an assign activity to convert the date. Snd assign it…
I am getting the error “String was not recognized as a valid DateTime”, even I have confirmed all column values are dates.
This is the statement I am using:
(From row In SalesReport Order By DateTime.ParseExact(row.Item(“Closing Date”).ToString, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable
and this is the full error message:
RemoteException wrapping System.FormatException: String was not recognized as a valid DateTime.
at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style) at lambda_method(Closure , DataRow ) at System.Linq.EnumerableSorter2.ComputeKeys(TElement[] elements, Int32 count) at System.Linq.EnumerableSorter1.Sort(TElement elements, Int32 count) at System.Linq.OrderedEnumerable1.<GetEnumerator>d__1.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() at System.Data.DataTableExtensions.LoadTableFromEnumerable[T](IEnumerable1 source, DataTable table, Nullable1 options, FillErrorEventHandler errorHandler) at System.Data.DataTableExtensions.CopyToDataTable[T](IEnumerable1 source) at lambda_method(Closure , ActivityContext ) at Microsoft.VisualBasic.Activities.VisualBasicValue1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context) at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor) at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath) at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary`2 argumentValueOverrides, Location resultLocation, Int32 startIndex) at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
you were almost done
kindly try with this expression
(From row In SalesReport Order By DateTime.ParseExact(row.Item(“Closing Date”).ToString.Substring(0,10), “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable
if this doesnt work either if possible can i have a view on the date value in the excel may be a screenshot if possible