I am trying to sort my data by time, but I have not been successful. My ActivityTime column is currently a string with HH:MM:SS AM/PM format. However, when I am trying to sort, it is not taking into account the AM/PM so it will look like this (when I do try something to get it to return values without an error code):

1:00:00 am
1:00:00 pm
2:00:00 am
2:00:00 pm

I am using BigQuery and I have tried several things, including mainly:

SELECT *
FROM `modern-ellipse-438015-c0.Bellabeat.HourlyCalories3-4` 
ORDER BY Id, ActivityDate, CAST(ActivityTime AS time)

I have also tried timestamp and some other things but they are all returning the error code:

Invalid time string "12:00:00 AM"

Can someone explain what this error message means exactly and why the code is not working? I have looked at other forum posts but the answers there aren't working for me.