TO_CHAR failing to set specific format in update

I’m trying to set a specific field in a record where the legacy code has used a DDMM format DATETIME as a char. I want to be able to find one specific value that matches something and basically change that one field. Super simple stuff, right?

update MY_TABLE 
set SOME_DATETIME = to_char('0111', 'DDMM') 
where 
FIELDA = 'AA' 
and FIELDB = '2' 
and to_char(SOME_DATETIME, 'DDMM') = '0311' 
and FIELDC = 'ABC'

I know for a fact that if I query using to_char(SOME_DATETIME, ‘DDMM’) = ‘0311’ in a where clause it works but I can’t seem to be able to get to change that field. I get the following error:

** ERROR: DBD::Oracle::st execute failed: ORA-01481: invalid number format model 

I’ve found plenty of examples where people have overcome this error message in a query but not in a “set”.

I’m new at Oracle so would love some help.

Thanks

Answer

It seems the data type of SOME_DATETIME is DATE, since query works for to_char(SOME_DATETIME, 'DDMM') = '0311' conversion. Then proper to use the following :

update MY_TABLE 
   set SOME_DATETIME = to_date('0111', 'DDMM') 
 where FIELDA = 'AA' 
   and FIELDB = '2' 
   and to_char(SOME_DATETIME, 'DDMM') = '0311' 
   and FIELDC = 'ABC';

where the true conversion should be performed by to_date instead of to_char.