1
votes

My data type is a date formatted as "YYYY-MON-DD" and I would like to extract the month and year to be formatted as "MON YYYY" while keeping the data type as date so that I will be able to use it with the ADD_MONTHS function. Is there a way to do so? I extract the date from the data field called date_process.

This is what I thought of but it doesnt seem to be working.

SELECT TO_DATE(TO_CHAR(PROCESS_DATE,'YYYY-MON'), 'MON YYYY') AS PERIOD,

Thank you.

1

1 Answers

1
votes

Dates are stored in an internal format, not as a string.

If you want to see the value in a particular format, then you need to convert it to a string. Hence, drop the final to_date():

SELECT TO_CHAR(PROCESS_DATE, 'MON YYYY') AS PERIOD,