population of start date attribute in correct format

[Abdul_Gaffar]

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

[sudeshjaiswal]

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,