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.
x
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'