0
466
Question Jude Mukkadayil · Oct 29, 2019

Cache Update Query

#Caché #Databases

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

Discussion (12)0
Log in or sign up to continue

Comments

Rizmaan Marikar · Oct 29, 2019

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.

0
Jude Mukkadayil  Oct 29, 2019 to Rizmaan Marikar

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?

0
Rizmaan Marikar  Oct 29, 2019 to Jude Mukkadayil

what is the data type of 

RESTR_DATETo

and can you post the error that you are getting

0
Rizmaan Marikar  Oct 30, 2019 to Jude Mukkadayil

Hi,

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

0
Robert Cemper · Oct 29, 2019

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.

0
Robert Cemper  Oct 30, 2019 to Jude Mukkadayil

It's a matter of design of the table.

pls. provide the table description

0
Steven Potashnyk · Oct 29, 2019

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.

0
Jude Mukkadayil  Oct 29, 2019 to Steven Potashnyk

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)
 

0
Vitaliy Serdtsev · Oct 30, 2019

Try this:

<FONT COLOR="#0000ff">update </FONT><FONT COLOR="#008000">RB_ResEffDateSessPayorRestr </FONT><FONT COLOR="#000080">set </FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#808000">to_date</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#808000">DATEADD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'year'</FONT><FONT COLOR="#000000">,1,</FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">),</FONT><FONT COLOR="#008080">'YYYY-MM-DD'</FONT><FONT COLOR="#000000">) </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">YEAR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">)=2020</FONT>
Or this:
<FONT COLOR="#0000ff">update </FONT><FONT COLOR="#008000">RB_ResEffDateSessPayorRestr </FONT><FONT COLOR="#000080">set </FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">=%odbcin(</FONT><FONT COLOR="#808000">DATEADD</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">'year'</FONT><FONT COLOR="#000000">,1,</FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">)) </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">YEAR</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">RESTR_DATETo</FONT><FONT COLOR="#000000">)=2020</FONT>
0
Jude Mukkadayil  Oct 30, 2019 to Vitaliy Serdtsev

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
0