End date needs to be empty on user import

[Robbe_Cronos]

Hello,

We are trying to import users from a csv file to an organization in Saviynt. The end date of users who are still with the organization is empty and an end date is only filled in when we know the date the user is going to leave. 

How can we add an empty end date in the enddate attribute of a user? We have tried adding a case in the SAV file which states:

"UPDATE NEWUSERDATA SET ENDDATE = CASE WHEN NEWUSERDATA.END_DATE = '' THEN '' WHEN NEWUSERDATA.END_DATE IS NULL THEN '' ELSE DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.START_DATE,'%Y-%m-%d'),'%Y-%m-%d') END"

This should give an empty enddate in the user when no enddate is given in the csv file & the end date formatted when there is a enddate given in the csv file.

This will give an error on the import job : Error in Users Import - Error while processing
data: Data truncation: Incorrect datetime value: ''

Is there another way we can approach this? 

Kind regards,

Robbe

[rushikeshvartak]

"UPDATE NEWUSERDATA SET ENDDATE = CASE WHEN NEWUSERDATA.END_DATE = '' THEN null WHEN NEWUSERDATA.END_DATE IS NULL THEN null ELSE DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.START_DATE,'%Y-%m-%d'),'%Y-%m-%d') END"

[Robbe_Cronos]

This unfortunately also does not work. I think the issue lies in the fact that if there is no enddate, the format for sql (d-m-y) is not correct so the job throws an error.

[rushikeshvartak]

"UPDATE NEWUSERDATA SET ENDDATE = CASE WHEN NEWUSERDATA.END_DATE = '' THEN DATE_FORMAT(STR_TO_DATE('2099-12-31','%Y-%m-%d'),'%Y-%m-%d')WHEN NEWUSERDATA.END_DATE IS NULL THEN DATE_FORMAT(STR_TO_DATE('2099-12-31','%Y-%m-%d'),'%Y-%m-%d') ELSE DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.START_DATE,'%Y-%m-%d'),'%Y-%m-%d') END"

[avinashchhetri]

Hello @Robbe_Cronos,

Can you share the date format of the enddate for the user when they do have one in the csv feed ?

[amit_krishnajit · Accepted solution]

Hi @Robbe_Cronos ,

Can you share the Modify User Data JSON you are trying to use? 

With the following sample Modify User Data JSON, I could set null to the user's enddate attribute.

{
	"ADDITIONALTABLES": {
		"USERS": "SELECT username, enddate FROM USERS"
	},
	"COMPUTEDCOLUMNS": [
		"enddate"
	],
	"PREPROCESSQUERIES": [
		"UPDATE NEWUSERDATA SET enddate= CASE WHEN enddate='' then NULL else DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.enddate,'%Y-%m-%d'),'%Y-%m-%d') END WHERE username is NOT NULL;"
	]
}

[Robbe_Cronos]

We already managed to fix it using a query like yours but forgot to close the post, thanks anyway!