You are very close, only a comma after the day is missing in your format string. Try
select parse_datetime("%b %e, %Y %I:%M:%S %p", "Sep 5, 2024 12:38:18 PM")