How to order a VARCHAR2 column representing date and time with AM and PM Oracle SQL?

Question

Hello I have the following column and I want to order it by date from earliest to latest. the format looks like this.

08/16/2019 08:09:51 AM

I came up with this but its giving me an error.

ORDER BY date_format(STR_TO_DATE(`END_TIME`,'%m-%d-%y %h:%i:%s %p'),'%Y-%m-%d %H:%i:%s')

I couldn't strictly order it by date because 2 PM came before 8 AM which is wrong so I tried to put it in the correct format, I feel like im close. Any ideas?

cast it as date should work I guess `order by cast(end_time as date)` — Standin.Wolf, Nov 19 '19 at 16:26
If `end_time` is a `date`, 2 pm would come after 8 am. Is `end_time` really a string representing a date? Are `str_to_date` and `date_format` functions that you've created? — Justin Cave, Nov 19 '19 at 16:27
@OldProgrammer its oracle but I got my answer from a similar overflow question guessing this wont work for oracle then. — stack flow, Nov 19 '19 at 16:28
Correct. Is the date stored as as VARCHAR2 type (bad), or an oracle DATE? If DATE type, you just order by END_TIME — OldProgrammer, Nov 19 '19 at 16:29
@OldProgrammer it actually is varchar2 is there a work around? or just remake the table? — stack flow, Nov 19 '19 at 16:30
look here https://stackoverflow.com/questions/14987473/convert-24-hour-time-to-12-hour-plus-am-pm-indication-oracle-sql — OldProgrammer, Nov 19 '19 at 16:30
If you are in the design phase of your project, I would refactor to use an actual `DATE` based type for a date value, rather than `VARCHAR2`. — gmiley, Nov 19 '19 at 16:32
Are you confused about Oracle company, which is also owner of MySQL DB, with Oracle DB ?This syntax doesn't belong to Oracle DB. — Barbaros Özhan, Nov 19 '19 at 17:52
@BarbarosÖzhan Op commented here that he has find this solution for mysql that he tried but it is Oracle that he uses... — VBoka, Nov 19 '19 at 18:53
Interesting ... I re-looked at the question and the comments and didn't encounter such a comment @VBoka — Barbaros Özhan, Nov 19 '19 at 18:57
@BarbarosÖzhan 4th from the top...Sorry, he did not mention that it is mysql but he reply's to the OldProgrammer who asked him that... — VBoka, Nov 19 '19 at 18:59

score 1 · Accepted Answer · answered Nov 19 '19 at 17:21

1

Maybe I am wrong but i do not see nothing wrong here:

select to_char(to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM'),'mm/dd/yyyy hh:mi:ss AM') 
from myTable
order by to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM');

That is whit this:

select *
from myTable
order by to_date(Date_c,'mm/dd/yyyy hh:mi:ss AM');

Here is the DEMO

answered Nov 19 '19 at 17:21

VBoka

8,995
3
16
24

1

This is the correct answer. Thank you so much :) my format was wrong i had AM before but I thought it wouldnt pick up the PM. – stack flow Nov 19 '19 at 17:57

How to order a VARCHAR2 column representing date and time with AM and PM Oracle SQL?

1 Answers1