Skip to content
Advertisement

ORA-01861: literal does not match format string use oracle SQL

I have data field in oracle database, i need to update my data field and use add_months function to add 3 more month as show below, my table name psm_voucher.

Name                            Null?    Type
------------------------------- -------- ----
BARCODE                                  VARCHAR2(20)
EXPDT                                    VARCHAR2(8)

Select barcode,expdt from psm_voucher where barcode='BBCV101670';

BARCODE              EXPDT
-------------------- --------
BBCV101670           20201231

then I try update query as below but fail

Update psm_voucher set expdt=add_months(expdt,3) where barcode='BBCV101670';
update psm_voucher set expdt=add_months(expdt,3) where barcode='BBCV101670'
                                        *
ERROR at line 1:
ORA-01861: literal does not match format string

Your kinds help is much appreciated.

Advertisement

Answer

As date in your table is stored as a string, you will need to follows some basic steps:

  • convert string to date
  • add 3 months into converted date
  • convert the date back to string which can be stored in your table.

Use the following query:

update psm_voucher 
   set expdt= to_char(add_months(to_date(expdt,'YYYYMMDD'),3), 'YYYYMMDD')
 where barcode='BBCV101670'
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement