CODEWITHSUNDEEP
sqloracle-databaseora-01481
solarflare
solarflare

Reputation: 441

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

Upvotes: -1

Views: 2876

Answers (3)

Nusret Onur Ayduman
Nusret Onur Ayduman

Reputation: 21

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

is a good way to improve autput and formatting

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65373

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.

Upvotes: 1

Ychdziu
Ychdziu

Reputation: 445

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

Upvotes: 1

Related Questions