Truncated incorrect date value error in preprocessor

[poonammhetre]

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

[PremMahadikar]

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]

@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

[PremMahadikar]

@poonammhetre,

This is issue then!

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 · Accepted solution]

{
"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())"
]
}

[poonammhetre]

@rushikeshvartak  Thanks a lot. Its working.