-3

I have data like :

MAR-16
APR-17
MAR-18
JUN-16

I want to sort it in ascending order based on date,but as the data is in character , I am not sure how to sort them based on date . Expected output is :

MAR-16
JUN-16
APR-17
MAR-18

Can someone please help .

joe
  • 173
  • 3
  • 17
  • order by to_date('01-' || column_name,'DD-MON-YY') asc; – Nikhil Shetkar Dec 18 '17 at 16:40
  • Hi, what have you tried? There are no shortages of questions like this. Here is an example, https://stackoverflow.com/questions/8366522/how-to-convert-a-string-date-to-date-format-in-oracle10g – Patrick Bacon Dec 18 '17 at 16:51
  • Possible duplicate of [how to convert a string date to date format in oracle10g](https://stackoverflow.com/questions/8366522/how-to-convert-a-string-date-to-date-format-in-oracle10g) – Patrick Bacon Dec 18 '17 at 16:51
  • Where should `JUN-97` be shown? Before `MAR-16` or after it? –  Dec 18 '17 at 17:20

1 Answers1

1

You can just do:

order by to_date(col, 'MON-YY')

But you should fix the column so it has a correct data type.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Wouldn't that, actually, be MON-DD, rather than MON-YY? True, the OP didn't specify whether "MAR-16" represents March 16th or March 2016, but I suspect that numeric part of those values is a DAY, not a year. Not your mistake, @Gordon, I'm just saying. – Littlefoot Dec 18 '17 at 19:39
  • @Littlefoot . . . Look at the desired ordering. It looks like a year, not a day. – Gordon Linoff Dec 19 '17 at 03:16
  • On a second thought, yes - you're most probably right. Which makes me wrong. Huh, two wrongs don't make right, but two Wrights made an airplane. – Littlefoot Dec 19 '17 at 06:50