Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

population of start date attribute in correct format

Abdul_Gaffar
New Contributor II
New Contributor II

Hi ,

we have a REST connection where the target start date attribute comes in the form as below:

06/15/2023 00:00:00.000000
 
however, we are trying to get in the format by using the preprocessor query as follows:
 
{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY,USERNAME,EMPLOYEEID,systemusername,startdate FROM USERS"
},
"COMPUTEDCOLUMNS": [
"customproperty20",
"DISPLAYNAME",
"Manager",
"startdate"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='Activedirectory'",
"UPDATE NEWUSERDATA SET DISPLAYNAME = CONCAT(lastname,'',ifnull(substring(middlename,1),''),'',firstname)",
"UPDATE NEWUSERDATA SET NEWUSERDATA.Manager=(select CURRENTUSERS.USERNAME from CURRENTUSERS where CURRENTUSERS.EMPLOYEEID=NEWUSERDATA.OWNER limit 1)",
"UPDATE newuserdata SET newuserdata.startdate = DATE_FORMAT(currentusers.startdate, '%d/%m/%Y') FROM currentusers WHERE newuserdata.Systemusername = currentusers.Systemusername"
]
}
 
please suggest us what shall be corrected as part of configuration 
 
Regards,
Abdul Gaffar
1 REPLY 1

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Abdul_Gaffar,

import start date from target app into some customproperty, say cp1 (where cp1 will have the value 06/15/2023 00:00:00.000000)
then use below calculation for startdate
"UPDATE NEWUSERDATA NU LEFT JOIN CURRENTUSERS CU ON NU.SYSTEMUSERNAME = CU.SYSTEMUSERNAME SET NU.STARTDATE = STR_TO_DATE(SUBSTRING_INDEX(NU.CUSTOMPROPERTY1, ' ', 1),'%m/%d/%Y')"

Thanks,

If you find the above response useful, Kindly Mark it as "Accept As Solution".