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

Truncated incorrect date value error in preprocessor

poonammhetre
New Contributor II
New Contributor II

Hi,

We have implemented the preprocessor query as below where the requirement is that when termdate is equal to current date then populate users cp21.

{
    "ADDITIONALTABLES": {
        "USERS": "SELECT employeeid,customproperty21,termdate,statuskey from users"
    },
    "COMPUTEDCOLUMNS": [
        "customproperty21"
    ],
    "PREPROCESSQUERIES": [
 
  "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid=CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 ='DISABLED' where NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE(STR_TO_DATE(NEWUSERDATA.TERMDATE,'%Y-%m-%d'))=DATE(CURDATE())"
    ]
}
 
Termdate format which we are receiving from source is like 2024-06-24T16:00:00Z
 
Getting below error in the logs.
 
"2024-06-06T06:12:57.859+00:00","ecm-worker","","null-qlznz","","2024-06-06T06:12:57.394512296Z stdout F 2024-06-06 06:12:57,394 [quartzScheduler_Worker-3] ERROR services.ImportSAvDataUserService - Error while processing data: "
"2024-06-06T06:12:57.859+00:00","ecm-worker","","null-qlznz","","2024-06-06T06:12:57.39451661Z stdout F com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Truncated incorrect date value: '2024-06-24T16:00:00Z'"
"2024-06-06T06:12:57.859+00:00","ecm-worker","","null-qlznz","","2024-06-06T06:12:57.39451987Z stdout F at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:104)"
 
Please help with this issue.
 
Thanks,
Poonam
5 REPLIES 5

PremMahadikar
All-Star
All-Star

Hi @poonammhetre ,

Can you try below:

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid=CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 ='DISABLED' where NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE(NEWUSERDATA.TERMDATE)=CURDATE()"

Let us know if this works!

 

if this helps your question, please consider selecting Accept As Solution and hit Kudos

poonammhetre
New Contributor II
New Contributor II

@PremMahadikar  Its not working, same error.  Is this because of the date format we have at source side. date format is like this 2024-06-24T16:00:00Z

@poonammhetre,

This is issue then!

PremMahadikar_0-1717800779465.png

Then below script should work:

"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid=CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 ='DISABLED' where NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE_FORMAT(NEWUSERDATA.TERMDATE,'%Y-%m-%d')=CURDATE()"

If this helps your question, please consider selecting Accept As Solution and hit Kudos

rushikeshvartak
All-Star
All-Star

{
"ADDITIONALTABLES": {
"USERS": "SELECT employeeid,customproperty21,termdate,statuskey from users"
},
"COMPUTEDCOLUMNS": [
"customproperty21"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.customproperty21 = 'DISABLED' WHERE NEWUSERDATA.TERMDATE != '' AND NEWUSERDATA.TERMDATE IS NOT NULL AND DATE(CONVERT_TZ(STR_TO_DATE(NEWUSERDATA.TERMDATE, '%Y-%m-%dT%H:%i:%sZ'), '+00:00', '+00:00')) = DATE(CURDATE())"
]
}


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.

poonammhetre
New Contributor II
New Contributor II

@rushikeshvartak  Thanks a lot. Its working.