Question

How do i convert a Short date to a long date in Oracle SQL?

for example.... Convert 09/12/2015 to show as 12th September, 2015

Answer 1

date_field is given by to_date

TO_DATE('09/12/2015', 'DD/MM/YYYY')

The conversion of the format is done by to_char which changes the format of the date

select trim(to_char(date_field, 'dd')) || 'th ' || to_char(date_field,'month, yyyy')
  from ...

so the final query would be

select trim(to_char(TO_DATE('09/12/2015', 'DD/MM/YYYY'), 'dd')) || 'th ' || to_char(TO_DATE('09/12/2015', 'DD/MM/YYYY'),'month, yyyy') from ...

if you use 'Month' in to_char it right pads to 9 characters; we have to use the abbreviated 'MON', or to_char then trim and concatenate