MODIFYUSERDATAJSON date filtering issue

[Hsoumare]

Hello, 

I've created an Rest connector to perform saviynt API call and modify identity data the importUserJson is working fine : 

i've filtered it first on my test identity : 

{
"connection":"userAuth",
"url":"https://domain.saviyntcloud.com/ECM/api/v5/getUser ",
"httpMethod":"POST",
"httpParams":"{\"advsearchcriteria\":{\"username\":\"savsavgen-7494384193906466062\"}}",
"httpHeaders":{
"Authorization":"${access_token}"
},
"httpContentType": "application/json",
"colsToPropsMap":{
"username":"username~#~char",
"siteID":"siteid~#~char",
"entity":"entity~#~char",
"statuskey":"statuskey~#~char",
"startdate":"startdate~#~char",
"termdate":"termDate~#~char",
"customproperty5":"Custom Property 5~#~char",
"customproperty6":"Custom Property 6~#~char",
"customproperty12":"Custom Property 12~#~char",
"customproperty22":"Custom Property 22~#~char",
"customproperty47":"Custom Property 47~#~char",
"customproperty60":"Custom Property 60~#~char",
"customproperty63":"Custom Property 63~#~char"
},
"pagination":{
"offset":{
"offsetParam":"offset",
"batchParam":"max",
"batchSize":500,
"totalCountPath":"completeResponseMap.totalcount"
}

},
"errorCode":"400",
"errorCodePath":"errorCode",
"userResponsePath":"userdetails"
}

but on the user MODIFYUSERDATAJSON i'm using a filter for the identity modification on my where clause :

{

   "ADDITIONALTABLES":{

      "USERS":"select DISTINCT entity, siteID, startdate, termdate, customproperty5, customproperty6, customproperty47, FROM USERS"

   },

   "COMPUTEDCOLUMNS":[

     "termdate",

      "customproperty5",

      "customproperty6",

      "customproperty47"

   ],

   "PREPROCESSQUERIES":[

     "update NEWUSERDATA a SET a.customproperty5='', a.customproperty47=1 WHERE a.customproperty6 IS NOT NULL AND a.customproperty47 IS NULL AND a.startdate >= now() AND a.startdate < DATE_ADD(now(),INTERVAL 1 DAY)"

   ]

}

without this condition a.startdate >= now() AND a.startdate < DATE_ADD(now(),INTERVAL 1 DAY) it is working fine but i want to filter on the date on my where clause.

Could you please advise on the correct format to use on the where condition ?

Regards

Harouna SOUMARE

[This message has been edited by moderator to mask URL]

[AmitM]

Hi @Hsoumare , can you try curdate() instead of now() OR use date(now()) instead of just now().

You could a select query first in data analyzer validating the syntax is working and then use it in update query in preprocessor. 

Thanks, Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[Hsoumare]

Hello AmitM,

its working on dataanalyser that's the problem.

Regards

Harouna SOUMARE

[AmitM]

Okay , give curdate and date(NOW()) a try. 

if that also doesn't work. Send the logs.

[Hsoumare · Accepted solution]

Hello finally find out the issue :

updated below on the ImportUserJson : 

"httpParams":"{\"userQuery\":\"termdate >='${Calendar calendar = Calendar.getInstance();calendar.add(Calendar.DAY_OF_YEAR, -60);return calendar.format('yyyy-MM-dd H:mm:ss')}'\"}",

and on modify userjson ; 

"PREPROCESSQUERIES":[
"update NEWUSERDATA a SET a.customproperty5='', a.customproperty47=1 WHERE a.customproperty6 IS NOT NULL AND a.customproperty47 IS NULL AND a.startdate >= DATE_ADD(now(), INTERVAL 1 DAY)"
]

Regards

Harouna SOUMARE