Cache Update Query

Primary tabs

Databases, Caché

Hi,

     I am trying to update an year on a date field in a cache database table but it showing an error message. But the functions are working on select query. The query I used is 

update RB_ResEffDateSessPayorRestr SET RESTR_DATETo = DATEADD(YYYY,1,RESTR_DATETo) where YEAR(RESTR_DATETo)=2020

I tried to update only the year which are 2020.

Can anyone please help me, Is there any error in the query?

Thanks

  • 0
  • 0
  • 112
  • 8
  • 4

Answers

Hi @Jude Mukkadayil 

Please try this;

DATEADD will return a timestamp, such as '2020-07-26 00:00:00',

Just need to convert it back to Date and insert

UPDATE RB_ResEffDateSessPayorRestr  
SET RESTR_DATETo = DATE(DATEADD('year',1,RESTR_DATETo)) 
WHERE YEAR(RESTR_DATETo)=2020

Hope this helps.

Rizmaan.

Still its not working.

I think its because of looping. Since I am using the same field in the where condition , that may be the issue.

Any idea?

what is the data type of 

RESTR_DATETo

and can you post the error that you are getting

Hi,

Is it possible to share the entire query / code that you are using to perform the update?

If RESTR_DATETo you try to change is part of the Idkey you can't do an UPDATE on it.

Instead you need an INSERT with new Contents and DELETE of the old.

It's a matter of design of the table.

pls. provide the table description

Hello Jude

  • What is the error?
  • Do you have change control enabled?

if your environment has CCR enabled you may need to create a change session before running an update query.

 

 

 

Error: [C:\Program Files (x86)\Synametrics Technologies\WinSQL\Winsql.exe]
[SQLCODE: <-105>:<Field validation failed in UPDATE>]
[Location: <ServerLoop>]
[%msg: <Field 'SQLUser.RB_ResEffDateSessPayorRestr.RESTR_DateTo' (value '2039-01-07 00:00:00') failed validation>] (State:S1000, Native Code: 69)
 

Try this:

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=to_date(DATEADD('year',1,RESTR_DATETo),'YYYY-MM-DD'where YEAR(RESTR_DATETo)=2020

Or this:

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=%odbcin(DATEADD('year',1,RESTR_DATETo)) where YEAR(RESTR_DATETo)=2020

Many Thanks . It worked very well.

I used this one only

update RB_ResEffDateSessPayorRestr set RESTR_DATETo=to_date(DATEADD('year',1,RESTR_DATETo),'YYYY-MM-DD'where YEAR(RESTR_DATETo)=2020