TO_CHAR failing to set specific format in update

Question

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

Kind advice..Please go through Oracle's documentation or some tutorials on `DATE` datatype before messing around with dates. — Kaushik Nayak, Apr 27 '18 at 05:48
`to_char('0111','DDMM')` attempts to convert the character string `'0111'` into a character string (the clue is in the name), which clearly can't be right. If you want a string then `'0111'` is already fine. — William Robertson, Apr 27 '18 at 09:23

score 1 · Answer 1 · answered Apr 27 '18 at 05:33

The error is in this part "set SOME_DATETIME = to_char('0111', 'DDMM')". When you use to_char function and specify a Date format, it expects a date. What does he get now? A string. Convert it to some Oracle Date and try again. for example:

select to_char(to_date('2018-01-01','yyyy-mm-dd'), 'DDMM') from dual

score 1 · Accepted Answer · answered Apr 27 '18 at 05:40

1

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.

answered Apr 27 '18 at 05:40

Barbaros Özhan

59,113
10
31
55

It did look like a date but using it as a char elsewhere confused me. Turns out it was a date. Cheers. – solarflare Apr 27 '18 at 05:48
@DenizC kolay gelsin dostum. – Barbaros Özhan Apr 27 '18 at 06:19

score 1 · Answer 3 · answered May 18 '22 at 07:18

1

Select TO_CHAR(date_time_Column,'DD\MM\YYYY HH24:MI)

is a good way to improve autput and formatting

answered May 18 '22 at 07:18

Nusret Onur Ayduman

21
3

TO_CHAR failing to set specific format in update

3 Answers3