Data truncation: Incorrect datetime value

[RajeshA]

We are getting error, when we configured below MODIFYUSERDATAJSON

{ "ADDITIONALTABLES": { "USERS": "SELECT username, statuskey, comments, startdate from USERS" }, "COMPUTEDCOLUMNS": [ "statuskey" ], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.statuskey=0 where CURRENTUSERS.statuskey=0 and NEWUSERDATA.statuskey=1 and currentusers.comments like '%Users are being terminated through Certification%' and CURRENTUSERS.startdate=NEWUSERDATA.startdate" ] }

2024-02-22/21:19:43.221 [{}] [quartzScheduler_Worker-3] DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_1837083 LEFT JOIN TEMPUSERS_1837083 ON TEMPNEWUSERS_1837083.USERNAME = TEMPUSERS_1837083.USERNAME SET TEMPNEWUSERS_1837083.statuskey=0 where TEMPUSERS_1837083.statuskey=0 and TEMPNEWUSERS_1837083.statuskey=1 and TEMPUSERS_1837083.comments like '%Users are being terminated through Certification%' and TEMPUSERS_1837083.startdate=TEMPNEWUSERS_1837083.startdate
2024-02-22/21:19:43.228 [{}] [quartzScheduler_Worker-3] ERROR services.ImportSAvDataUserService - Error while processing data:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '01/11/1993' for column 'startdate' at row 167
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4206)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1759)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure9.doCall(ImportSAvDataUserService.groovy:362)
at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:351)
at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:614)
at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3066)
at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2919)
at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:617)
at UserImportJob.execute(UserImportJob.groovy:108)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)

Currently in KIM we are getting startdate in below format MM/dd/yyyy

I also see in preference we are using below date format

Can you please let us know what is the issue, we are on SP5.5sp3. We want to make sure the users who are terminated through campaign are not enabled back, if that same user still active in workday (with same startdate). 

[CR]

can you try below ref link scenario may it will help you

https://forums.saviynt.com/t5/identity-governance/how-to-convert-date-format-from-mm-dd-yyyy-to-savi...

[RajeshA]

Hello CR,

I had looked at this scenario you pasted, before creating forum and it don't work here for two reasons.

1. We don't want to run DB job in trigger chain after workday import job. By that time, workday will already enable the user back and cause the provisioning to downstream applications. In the scenario you asked me to look into, they don't have this restriction. They can run that SAV4SAV DB job even after 1 hr or x number of hrs after HR import job, which doesn't work in my case. 

2. We came to know that SAV4SAV DB will be shifted to REST connection based SAv4SAv, as we are about to migrate to 2024 version. and we don't want yo go through that extra overhead 

So please let us know, if this is not possible at all by using preprocessory query. 

[rushikeshvartak]

{
"ADDITIONALTABLES": {
"USERS": "SELECT username, statuskey, comments, startdate from USERS"
},
"COMPUTEDCOLUMNS": [
"statuskey"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.statuskey=0 where CURRENTUSERS.statuskey=0 and NEWUSERDATA.statuskey=1 and currentusers.comments like '%Users are being terminated through Certification%' and STR_TO_DATE(NEWUSERDATA.startdate, '%m/%d/%Y')=NEWUSERDATA.startdate"
]
}

[AmitM]

STR_TO_DATE(CURRENTUSERS.startdate, '%m/%d/%Y')=NEWUSERDATA.startdate

@RajeshA , the startdate in users table is date and time data type. In new user data as you showed in screen shot is just date no time. So you need to convert one to compare with other.

Thanks,

Amit

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[RajeshA · Accepted solution]

Hello @AmitM , thanks for giving background on this.

I did try the way you mentioned

{ "ADDITIONALTABLES": { "USERS": "SELECT username, statuskey, comments, startdate from USERS" }, "COMPUTEDCOLUMNS": [ "statuskey" ], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.statuskey=0 where CURRENTUSERS.statuskey=0 and NEWUSERDATA.statuskey=1 and currentusers.comments like '%Users are being terminated through Certification%' and STR_TO_DATE(CURRENTUSERS.startdate, '%m/%d/%Y')=NEWUSERDATA.startdate" ] }

And it gave me below error

2024-02-23/13:33:33.610 [{}] [quartzScheduler_Worker-19] DEBUG services.ImportSAvDataUserService - Processing query: UPDATE TEMPNEWUSERS_1837084 LEFT JOIN TEMPUSERS_1837084 ON TEMPNEWUSERS_1837084.USERNAME = TEMPUSERS_1837084.USERNAME SET TEMPNEWUSERS_1837084.statuskey=0 where TEMPUSERS_1837084.statuskey=0 and TEMPNEWUSERS_1837084.statuskey=1 and TEMPUSERS_1837084.comments like '%Users are being terminated through Certification%' and STR_TO_DATE(TEMPUSERS_1837084.startdate, '%m/%d/%Y')=TEMPNEWUSERS_1837084.startdate
2024-02-23/13:33:33.614 [{}] [quartzScheduler_Worker-19] ERROR services.ImportSAvDataUserService - Error while processing data:
java.sql.SQLException: Incorrect datetime value: '1993-01-11 00:00:00' for function str_to_date
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1094)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4208)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4140)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2597)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2758)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2820)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1759)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1679)
at com.saviynt.ecm.services.ImportSAvDataUserService$_doImportDataPreprocessing_closure9.doCall(ImportSAvDataUserService.groovy:362)
at com.saviynt.ecm.services.ImportSAvDataUserService.doImportDataPreprocessing(ImportSAvDataUserService.groovy:351)
at com.saviynt.ecm.services.ImportSAvDataUserService.importDataFromFile(ImportSAvDataUserService.groovy:614)
at com.saviynt.provisoning.rest.RestProvisioningService.processUsers(RestProvisioningService.groovy:3066)
at com.saviynt.provisoning.rest.RestProvisioningService.importUsers(RestProvisioningService.groovy:2919)
at com.saviynt.ecm.integration.ExternalConnectionCallService.importUserUsingExternalConnection(ExternalConnectionCallService.groovy:617)
at UserImportJob.execute(UserImportJob.groovy:108)
at org.quartz.core.JobRunShell.run(JobRunShell.java:199)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:546)
2024-02-23/13:33:33.615 [{}] [quartzScheduler_Worker-19] DEBUG services.ImportSAvDataUserService - Start dropTempTables
2024-02-23/13:33:33.615 [{}] [quartzScheduler_Worker-19] DEBUG services.ImportSAvDataUserService - Dropping table TEMPNEWUSERS_1837084
2024-02-23/13:33:33.615 [{}] [quartzScheduler_Worker-19] DEBUG services.ImportSAvDataUserService - Executing Qry: DROP TABLE IF EXISTS ssminlp.TEMPNEWUSERS_1837084

After checking the logs I did see that for temp table STARTDATE is getting created as VARCHAR(255)

so I change to below and IT WORKED!!!

{ "ADDITIONALTABLES": { "USERS": "SELECT username, statuskey, comments, startdate from USERS" }, "COMPUTEDCOLUMNS": [ "statuskey" ], "PREPROCESSQUERIES": [ "UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.USERNAME = CURRENTUSERS.USERNAME SET NEWUSERDATA.statuskey=0 where CURRENTUSERS.statuskey=0 and NEWUSERDATA.statuskey=1 and currentusers.comments like '%Users are being terminated through Certification%' and CURRENTUSERS.startdate=STR_TO_DATE(NEWUSERDATA.startdate, '%m/%d/%Y')" ] }

[AmitM]

@RajeshA , good to hear you could find a way to make it work.

Thanks,

Amit