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'