Data truncation: Truncated incorrect date value

[Suresh1]

Hi Team,

I'm trying to incorporate a modifyuserdatajson in one of the Workday REST connections. The use case here is to nullify the termdate upon checking certain conditions. But i'm getting Error while processing data: Data truncation: Truncated incorrect date value: ''

Below is the error screenshot

Below is the query:

  {
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate from USERS" },
"COMPUTEDCOLUMNS": [ "statuskey","termdate" ],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate=STR_TO_DATE('', '%m/%d/%Y') where NEWUSERDATA.statuskey=1 and CURRENTUSERS.statuskey=0 and (CURRENTUSERS.termdate is not null or CURRENTUSERS.termdate != '') and CURRENTUSERS.username='XXXXX'"
]
}

Could you please help me with the right syntax to achieve this use case ?

Regards,

Suresh V.

[stalluri]

@Suresh1  update it to below and see.

Single user:
"UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS
ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME
SET CURRENTUSERS.termdate = NULL
WHERE NEWUSERDATA.statuskey = 1
AND CURRENTUSERS.statuskey = 0
AND (CURRENTUSERS.termdate IS NOT NULL OR CURRENTUSERS.termdate != '')
AND CURRENTUSERS.username = 'XXXXX'"

All users

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate FROM USERS"
  },
  "COMPUTEDCOLUMNS": [ "statuskey", "termdate" ],
  "PREPROCESSQUERIES": [
    "UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND (CURRENTUSERS.termdate IS NOT NULL OR CURRENTUSERS.termdate != '')"
  ]
}

[Suresh1]

Hi @stalluri , thank you for your reply.

I have tried both the queries, Interestingly i get unknown column statuskey exception.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate FROM USERS"
},
"COMPUTEDCOLUMNS": [ "termdate","statuskey" ],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND (CURRENTUSERS.termdate IS NOT NULL OR CURRENTUSERS.termdate != '') AND CURRENTUSERS.username = 'XXXXX'"
]
}

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate FROM USERS"
  },
  "COMPUTEDCOLUMNS": [ "statuskey", "termdate" ],
  "PREPROCESSQUERIES": [
    "UPDATE CURRENTUSERS LEFT JOIN NEWUSERDATA ON CURRENTUSERS.USERNAME = NEWUSERDATA.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND CURRENTUSERS.termdate IS NOT NULL AND CURRENTUSERS.termdate != ''"
  ]
}

Refer https://forums.saviynt.com/t5/identity-governance/truncated-incorrect-date-value-error-in-preprocess...

[Suresh1]

Hi @rushikeshvartak , thank you for your response.

Do you want me to try with AND condition ? 

CURRENTUSERS.termdate IS NOT NULL AND CURRENTUSERS.termdate != ''"

[stalluri]

@Suresh1 

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate FROM USERS"
},
"COMPUTEDCOLUMNS": [ "termdate","statuskey" ],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND CURRENTUSERS.termdate IS NOT NULL AND CURRENTUSERS.termdate != '' AND CURRENTUSERS.username = 'XXXXX'"
]
}

[Suresh1]

Tired it but no luck same error again.

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate,statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND (CURRENTUSERS.termdate IS NOT NULL OR CURRENTUSERS.termdate != '') AND CURRENTUSERS.username = 'XXXXX'"
  ]
}

[Suresh1]

Now getting Data truncation: Incorrect datetime value: '' for column 'termdate' 

[rushikeshvartak]

Make sure you enlarge image  or send logs in text format

https://forums.saviynt.com/t5/forum-feedback/enlarge-image-by-default/m-p/96947

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate, statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND (CURRENTUSERS.termdate IS NOT NULL AND CURRENTUSERS.termdate != '' AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL) AND CURRENTUSERS.username = 'XXXXX'"
  ]
}

[Suresh1]

@rushikeshvartak , Below are the logs.

2024-09-12/18:35:54.721 [{}] [quartzScheduler_Worker-4] DEBUG services.ImportSAvDataUserService - Executing the pre-processing queries
2024-09-12/18:35:54.725 [{}] [quartzScheduler_Worker-4] DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_1880445 LEFT JOIN TEMPUSERS_1880445 ON TEMPNEWUSERS_1880445.USERNAME = TEMPUSERS_1880445.USERNAME SET TEMPUSERS_1880445.termdate = NULL WHERE TEMPNEWUSERS_1880445.statuskey = 1 AND TEMPUSERS_1880445.statuskey = 0 AND (TEMPUSERS_188045.termdate IS NOT NULL AND TEMPUSERS_1880445.termdate != '' AND STR_TO_DATE(TEMPUSERS_1880445.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL) AND TEMPUSERS_1880445.username = 'XXXXX'
2024-09-12/18:35:54.730 [{}] [quartzScheduler_Worker-4] ERROR services.ImportSAvDataUserService - Error while processing data:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'termdate' at row 1

Below JSON worked.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate, statuskey FROM USERS"
},
"COMPUTEDCOLUMNS": [
"termdate",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL AND CURRENTUSERS.username = 'XXXX'"
]
}

So, Removed the currentusers.termdate is not null condition and just have the below one 

STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL)

[stalluri]

@Suresh1 
Can you try this.

CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate =''

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate, statuskey FROM USERS"
},
"COMPUTEDCOLUMNS": [
"termdate",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND STR_TO_DATE(NULLIF(CURRENTUSERS.termdate, ''), '%Y-%m-%d %H:%i:%s') IS NOT NULL AND CURRENTUSERS.username = 'XXXX'"
]
}

[Suresh1]

This syntax did work to set termdate to null but seems like its not evaluating conditions in the where clause. Below is my Json. Termdate is getting updated with NULL value even though where condition fail.

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, termdate, statuskey FROM USERS"
},
"COMPUTEDCOLUMNS": [
"termdate",
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate = NULL WHERE STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') < CURDATE()"
]
}

[rushikeshvartak]

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate, statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE CURRENTUSERS SET CURRENTUSERS.termdate = NULL WHERE STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') < CURDATE();"
  ]
}

[Suresh1]

@rushikeshvartak , Thanks for the update. Tried this and not working as expected, Termdate is getting set with NULL value even though it's not less than current date. Query satisfied for users which termdate is in future date also. 

I'm seeing the same behavior in both v5.5 and EIC 24.4 versions.

2024-10-07T13:10:05+05:30-ecm-worker--null-4tq5r--"UPDATE CURRENTUSERS SET CURRENTUSERS.termdate = NULL WHERE STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') < CURDATE();"

[rushikeshvartak]

What is your current vs imported user data termdate value ?

[Suresh1]

@rushikeshvartak ,We are not getting Termdate from the import. We are checking current users termdate should not be future date.

For instance, Termdate set to 31st Oct 2024, But when i run the import termdate sets to null value.

Below is the Json i used 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate, statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE CURRENTUSERS SET CURRENTUSERS.termdate = NULL WHERE STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') IS NOT NULL AND STR_TO_DATE(CURRENTUSERS.termdate, '%Y-%m-%d %H:%i:%s') < CURDATE();"
  ]
}

 Post running the import job with above query

[rushikeshvartak]

• Does Termdate  mapping also there is json ? did u tried removing ?
• How are you testing with import sheet ?

[Suresh1]

• Does Termdate  mapping also there is json ? did u tried removing ? -> No, We dont have termdate mapping in import user json. Termdate gets populated for the user from a different RaaS report. Below is the current importuser json.

{
 "url": "<URL>",
 "connection": "acctAuth",
 "httpMethod": "GET",
  "httpContentType": "application/json",
 "httpHeaders": {
   "Accept": "*/*",
   "Authorization": "${access_token}"
 },
 "userResponsePath": "Report_Entry",
 "colsToPropsMap": {
   
	"CUSTOMPROPERTY40":"Effective_Date_of_Termination~#~date",
	"CUSTOMPROPERTY41":"initiated~#~char",
	"CUSTOMPROPERTY30":"Rescinded_by_Worker~#~char",
	"CUSTOMPROPERTY31":"transactionStatus~#~char",
	"CUSTOMPROPERTY32":"initiator~#~char",
	"CUSTOMPROPERTY33":"Date_Rescinded~#~date",
	"MANAGER":"Manager~#~char",
	"lastname":"Legal_Name_-_Last_Name~#~char",
	"CUSTOMPROPERTY2":"RACF_ID~#~char",
	"username": "Employee_ID~#~char",
	"firstname":"Legal_Name_-_First_Name~#~char",
	"MIDDLENAME":"Legal_Name_-_Middle_Name~#~char",
	"CUSTOMPROPERTY42":"Location_Address_-_Country~#~char",
	"CUSTOMPROPERTY36":"Active_Status_with_Date~#~char",
	"CUSTOMPROPERTY8":"Coalition~#~char",
	"CUSTOMPROPERTY37":"Location~#~char",
	"CUSTOMPROPERTY7":"Supervisory_Organization~#~char"
 }
}

• How are you testing with import sheet ?  I'm running 

  User Import via a Connection (UserImportJob) 

   job to fetch the users from a WD RaaS report with MODIFYUSERDATAJSON. I need to set the term date to null for users whos termdate is past date. FYI, Termdate gets updated to the user profile from a different import, and then if user comes in resc terminations report (Current one) we are trying to wipe out the termdate if it is past date. so that our termination process will not pick this user with empty termdate.

[rushikeshvartak]

Does that records come from current report

[Suresh1]

Yes, it does. 

Checking a value which is not part of the import is not allowed in preprocessor ? For eg, Termdate is not coming in the WD RaaS report but i'm trying to compare currentusers.termdate here. To check this, i have tested with below query only with username condition interestingly termdate got emptied even though the username is different. I'm getting the username ABC in the report but in where condition i have given username as XYZ still termdate is wiped out.

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate, statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE CURRENTUSERS SET CURRENTUSERS.termdate = NULL WHERE CURRENTUSERS.username='UsernameXYZ';"
  ]
}

  Any suggestions on this kind of behavior is highly appreciable 

Regards,

Suresh V.

[stalluri]

@Suresh1 

{
  "ADDITIONALTABLES": {
    "USERS": "SELECT username, termdate,statuskey FROM USERS"
  },
  "COMPUTEDCOLUMNS": [
    "termdate",
    "statuskey"
  ],
  "PREPROCESSQUERIES": [
    "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET CURRENTUSERS.termdate ='' WHERE NEWUSERDATA.statuskey = 1 AND CURRENTUSERS.statuskey = 0 AND (CURRENTUSERS.termdate IS NOT NULL OR CURRENTUSERS.termdate != '') AND CURRENTUSERS.username = 'XXXXX'"
  ]
}

[Suresh1]

Tried this, But no luck same error.

ERROR services.ImportSAvDataUserService - Error while processing data:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '' for column 'termdate' at row 1