Sort by date time not working

i would like to sort my data table. the data is populated from the attached excel file.
Status.xlsx (8.6 KB)
i’ve tried using the regular sort activity, but that does not sort properly.
so i try the following:
(From row In status_log_dt Order By DateTime.ParseExact(row.Item(“date time1”).ToString, “dd/MM/yyyy”, System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable

the sequence fail at assign activity with the code above, then a error saying that the format is invalid.

if ‘date time1’ column is really a datetime, then maybe the format (“dd/MM/yyyy”) is wrong and you have a day bigger than 12 being treated as a month?

thanks for the quick reply!
i’ve tried several format settings, could you tell me what should the correct format expression?
below is the what the data looks like
image

@Jason_Yao Change it the format in your datetime.parse so it is “MM/d/yyyy” It is possible it needs to be “M/d/yyyy” as well. If it September = 09, use “MM” and if September = 9, use “M”

1 Like

to be honest i find it hard to believe that the Sort Data Table activity would not work properly :frowning:

it would sort 10/7 to the top desc when you intent to sort the most recent datetime. looks like the sort activity only sorting it alphabetically, and it just dont have the option to change the sort datatype.

but how did you come with the conclusion it is not working to sort by the datetime column? because if the format is wrong, then month and day might be misplaced…

I think converting to datetime and sorting after is fastest. It is already not in sortable string format as it would have to be yyyy-mm-dd format, otherwise January 1 2018 would come before December 1 2017.

As long as you’re converting, you may as well convert it to datetime

thanks guys, Dave help me figure it out. it looks like i just over complicated things.
the code below help me sort the most recent date time to the top.

(From row In status_log_dt Order By DateTime.Parse(row.Item(“date time1”).ToString) descending Select row).CopyToDataTable

the code i was using initially was from another thread, and i notice most solutions suggested using datetime.parseexact with “dd/mm/yyyy” format. i’ve tried several other formats as well, none had worked…

it looks like datetime.parse does not require me to specify the format, so it would do just fine for my needs.

do any of you see any potential future problems from using parse instead of parseexact?

in my opinion yes, i am a developer for over 20 years and i have my own rule since creation of the object oriented languages, i never convert datetime to string unless it is on the frontend and i can assure you the regular activity to sort by date is working as expected as i just tested it here, so if you see something wrong, then it must be your data… :wink:

@Jason_Yao Parse is fine as long as it stays in the current format. You can also just use CDate() which is the same thing but easier to read in my opinion. This is using the current culture info (likely invariant culture, which is essentially US culture) to try and parse the string into a datetime. As long as it stays in US format of “MM/d/yyyy” or “m/d/yyyy” then it should be fine

but looking at his print, how do you know which culture he is at? it just doesnt make sense to me to take something that is in datetime format, convert it to string and then parse it back to datetime to then sort it…

i’m curious what i did wrong in the sort data activity…
below is my property setting for the activity. could you tell me what i did wrong?
image
here is the output, as you can see 10/7 dates are sorted to the top.

i’m from usa, so we’re good here :slight_smile:

thats my point it might be that the system is taking the format like 10 is the day and 7 (6) is the month… oh, but you have a 16 there, so dont use the datatable output to prove that, but really inspect the object of output sort dt activity.

i’m still pretty new to uipath, so is datatable output frown upon? and how do you inspect object? do you do that during run time? also, do we have a consensus that sort datatable is not going to work for my situation?

Just based on the way the datetime is structured, since US is one of the only “MM/dd/yyyy” cultures. And for whatever reason, he is not getting datetime and it appears to be coming in as a string. Good point though, that’s a good debug step - @Jason_Yao what is the output (before doing the conversion) if you put the following in a write line activity: status_log_dt.columns(2).DataType.ToString

like this:
Capture

2 Likes

system.object

awesome, i learned a new trick! this is going to save me a ton of time. thanks dude! does it also tell me the data type of each column?