I'm trying to change a date but keep getting the error "Not a Valid Month". I want to change a date such as 7/23/2012 to JUL_12.
This is what I'm currently doing:
UPDATE DISREQ DRQ
SET DRQ.REQUEST_DATE =
SUBSTR(TO_CHAR(TO_DATE(DRQ.REQUEST_DATE, 'MM/DD/YY')), 4, 3 ) || '_' || SUBSTR(TO_CHAR(TO_DATE(DRQ.REQUEST_DATE, 'MM/DD/YY')), 8 )
Any help would appreciated.
This would be an sql question I guess.
Note however that date are a type of number and they stay that way. If you have a variable that is a date
then you cannot assign characters to it. You can only assign a date to it.
suppose DRQ.REQUEST_DATE is a DATE. then you could do this:
select to_char(DRQ.REQUEST_DATE,"MON_YY") from dual;
I think you are trying to change 7/23/2012 to JUL_12 i.e from date to character
Please change the datatype of the column and do it. It will throw you the error.
Or if you want to keep the datatype of the field as DATE means you shouldn't use TO_CHAR function as most people do...
Hope this helps,