3

I am trying to convert the date from YYYYMMDD to DD-Mon-YYYY in Oracle, but to_char or to_Date is not working. Can you please advise?

select to_date(20150324,'DD-Mon-YY') from dual; select to_char(20150324,'DD-Mon-YY') from dual;

I get an error message saying: - ORA-01861: literal does not match format string

user272735
  • 10,473
  • 9
  • 65
  • 96
Avinash Ganesh
  • 423
  • 3
  • 6
  • 10
  • 1
    ["TO_DATE converts [characters\] .. to a value of DATE datatype."](http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm) - see what the function does, and how the parameters are used, for starters. – user2864740 Mar 25 '15 at 18:28
  • possible duplicate of [change dd-MMM-yy date format to yyyy-MM-dd format in oracle](http://stackoverflow.com/questions/28298838/change-dd-mmm-yy-date-format-to-yyyy-mm-dd-format-in-oracle) – Lalit Kumar B Mar 25 '15 at 18:54

1 Answers1

7

Use this combination of to_char and to_date:

select to_char (to_date('20150324','YYYYMMDD'), 'DD-Mon-YY') from dual;

Your mistake was, that you used the wrong date pattern. Additionally it's recommended to add'', though it worked without them in this case.

Check this Fiddle.

Trinimon
  • 13,839
  • 9
  • 44
  • 60
  • @Trinimon, the error is not due to the missing quotes, though it would error out for sure. But the posted error is due to mismatch in the format mask. – Lalit Kumar B Mar 25 '15 at 18:49
  • Yes, you're right: quotes were missing but it worked without `''` as well. Though I think there was an implicit type conversion which is not really recommended. I'll add a note on the format in the text (fixed it in the Fiddle but missed to mention it above) – Trinimon Mar 25 '15 at 19:02
  • don't you end up with a char column and not a date column doing this? – mLstudent33 Feb 26 '20 at 23:55
  • 1
    @mLstudent33: a date column has no format at all. The format is a matter of date representation. My code above converts the representation '20150324' into the representation '24-Mar-15', intermediately using a date for the conversion. Hope that helps :) – Trinimon Feb 27 '20 at 14:51