Issue with Date comparison in PREPROCESSQUERIES

[Diptansu1]

Hello Team , 

There is one scenario where i need to set users STATUSKEY=1 if ENDDATE is null or ENDDATE>SYSDATE() and 0 if otherwise.

For that using this query : UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = case when DATE(NEWUSERDATA.ENDDATE) is null then 1 when DATE(NEWUSERDATA.ENDDATE) is not null and DATE(NEWUSERDATA.ENDDATE) > DATE(sysdate()) then 1 when DATE(NEWUSERDATA.ENDDATE) is not null and DATE(NEWUSERDATA.ENDDATE) < DATE(sysdate()) then 0 end.

Still getting error as below:

Error in Users Import - Error while processing
data: Data truncation: Incorrect datetime value:
'Aug 12, 2025 00:00:00'

for the sample user i have set: Startdate=15th Aug 2023, Enddate=15th Aug 2025. The same logic is working in Data Analyser in query and fetching correct results. Can you pls suggest what i need to change in preprocessor query to make it work.

[sudeshjaiswal]

Hello @Diptansu1 ,

could you please try with the below query,

"UPDATE NEWUSERDATA SET STATUSKEY=CASE WHEN (ENDDATE IS NOT NULL AND ENDDATE < SYSDATE()) THEN 0 WHEN ENDDATE IS NULL THEN 1 WHEN (ENDDATE IS NOT NULL AND ENDDATE > SYSDATE()) THEN 1 END"

Thanks

[Diptansu1]

Tried the same same exception persists

[sudeshjaiswal]

Hi @Diptansu1 

Could you kindly provide the complete JSON? Additionally, can you test the following simple query to determine if it functions correctly?

"UPDATE NEWUSERDATA SET STATUSKEY = CASE WHEN ENDDATE < SYSDATE() THEN 0 ELSE 1 END"

Thank you.

[Diptansu1]

We have made the same format for both sysdate() and enddate as below.

I mean same format like DD-mm-YYY HH-MM-SS format. So ideally this should compare . But whenever > or < operator we are using then truncation error is coming.

[Diptansu1]

Hello All , 

We tried all possible solutions :

1. ENDDATE<SYSDATE()
2. ENDDATE<NOW()
3. DATE(ENDDATE)<DATE(SYSDATE())
4. DATEDIFF(ENDDATE,SYSDATE())
5. TIMESTAMP(ENDDATE)<TIMESTAMP(SYSDATE())
6. Saving these values in customproperty14&15. Then comparing DATE(CUSTOMPROPERTY14)<DATE(CUSTOMPROPERTY15)

all of these are fetching results from datanalyzer. But when ever DATE comparison is encountering in SAV files json then its failing saying :Error while processing
data: Data truncation: Incorrect datetime value

[sudeshjaiswal]

Hello @Diptansu1,

Can you please provide the complete json.

Thanks.

[Diptansu1]

Please find as below:

#MODIFYUSERDATAJSON={ "ADDITIONALTABLES" : { "USERS" : "SELECT USERKEY,EMAIL,USERNAME,MANAGER FROM USERS" }, "COMPUTEDCOLUMNS" : ["PHONENUMBER","EMAIL","USERNAME","ENDDATE","STARTDATE","STATUSKEY","CUSTOMPROPERTY13","CUSTOMPROPERTY14"], "PREPROCESSQUERIES" : ["UPDATE NEWUSERDATA SET NEWUSERDATA.PHONENUMBER=100","UPDATE NEWUSERDATA SET NEWUSERDATA.MANAGER=(select CURRENTUSERS.USERNAME from CURRENTUSERS where CURRENTUSERS.EMAIL=NEWUSERDATA.MANAGER)","UPDATE NEWUSERDATA SET NEWUSERDATA.USERNAME = case when NEWUSERDATA.EMAIL is null OR NEWUSERDATA.EMAIL like '' then NEWUSERDATA.EMPLOYEEID else NEWUSERDATA.EMAIL end","UPDATE NEWUSERDATA SET NEWUSERDATA.ENDDATE = DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.CUSTOMPROPERTY12, '%d/%m/%Y'), '%b %d, %Y %H:%i:%s')","UPDATE NEWUSERDATA SET NEWUSERDATA.STARTDATE = DATE_FORMAT(STR_TO_DATE(NEWUSERDATA.CUSTOMPROPERTY11, '%d/%m/%Y'), '%b %d, %Y %H:%i:%s')","UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY13=DATE_FORMAT(sysdate(),'%b %d, %Y %H:%i:%s')","UPDATE NEWUSERDATA SET NEWUSERDATA.CUSTOMPROPERTY14=NEWUSERDATA.ENDDATE","UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = case when NEWUSERDATA.CUSTOMPROPERTY14 is null then '1' when NEWUSERDATA.CUSTOMPROPERTY14 > NEWUSERDATA.CUSTOMPROPERTY13 then '1' when NEWUSERDATA.CUSTOMPROPERTY14 < NEWUSERDATA.CUSTOMPROPERTY13 then '0' end"]}

[Diptansu1]

Please fine the JSON as above

[sudeshjaiswal]

Hello @Diptansu1,

I dont know why you needs the format change for endddate and sysdate, it wont work that way what you comparing cp14 and cp13. you can try just with the below  change:

if CUSTOMPROPERTY12 is in the format '%d/%m/%Y', then try below changes:-

"UPDATE NEWUSERDATA SET NEWUSERDATA.ENDDATE = STR_TO_DATE(NEWUSERDATA.CUSTOMPROPERTY12, '%d/%m/%Y')"
"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = case when NEWUSERDATA.ENDDATE is null then '1' when NEWUSERDATA.ENDDATE > SYSDATE() then '1' when NEWUSERDATA.ENDDATE < SYSDATE() then '0' end"


Thanks,

[Diptansu1]

Hello, can you kindly advise on this please. This is still failing.

[Diptansu1]

We have tried this and whenever NEWUSERDATA.ENDDATE > SYSDATE() is getting checked then only Data truncation: Incorrect datetime value is coming

[sudeshjaiswal]

Hello @Diptansu1,

Can you please confirm the source of data and which connector you are using, please also mention the Job type whether it is full import or incremental import.
Instead the EndDate, you are storing the date in customproperty12. Can you please try using customproperty12 and run the below query,
"UPDATE NEWUSERDATA SET NEWUSERDATA.STATUSKEY = case when NEWUSERDATA.ENDDATE is null then '1' when NEWUSERDATA.CUSTOMPROPERTY12 > SYSDATE() then '1' when NEWUSERDATA.CUSTOMPROPERTY12 < SYSDATE() then '0' end"

[Diptansu1]

We tried this earlier, the problem is Customproperty is of String datatype and SYSDATE() is of DATE datatype so we cannot just use Greater than or less than comparison between these 2 owing to the fact that they are of different data types. And again if we convert the customrproperty into DATE and then do the comparison same error is coming. so here lies my observation whenever we are comparing between two dates thats failing in saviynt.

And coming to source of data thats the csv file and sav file we created for 1/2 users and uploaded under filedirectory and running the File based Users Import (SchemaUserJob)  task

[This message has been edited by moderator to remove hyperlink]

[Darshanjain]

Hi @Diptansu1 

Can you try this directly using the enddate column instead of customproperty12. ( Also in the mentioned json if you are using cp12, u need to fetch that value from Users when mentioned in Additional tables )

"UPDATE NEWUSERDATA SET STATUSKEY= CASE WHEN  ((FROM_UNIXTIME(CONVERT(SUBSTRING_INDEX(SUBSTRING_INDEX(NEWUSERDATA.ENDDATE, ')', 1), '(', -1),UNSIGNED INTEGER) DIV 1000)) >=CURDATE()) THEN 1 ELSE 0 END",

You can update the logic as per your requirement 

Thanks

Darshan

[Diptansu1]

Thanks just tried this, getting error as below :

Error in Users Import - Error while processing
data: Data truncation: Truncated incorrect INTEGER
value: 'Oct 12, 2021 00:00:00'

The error is same just this time instead of date it's coming on INTEGER . Means any comparison operation is not working. 

[Darshanjain]

If i am right Oct12 is the enddate mentioned right?

Also let me know the exact timestamp how it is stored in enddate

Thanks

Darshan

[Diptansu1]

Yes correct , As we can see below left side is ENDDATE , right side is SYSDATE() moreover both are in same format.

[Diptansu1]

Hello , can you kindly update on this please

[Darshanjain]

Hi @Diptansu1 

Saviynt expects the date format to be yyyy-mm-dd ( Also check in global config which time format you have selected ).

So in the csv file upload the enddate as in the above format and then use curdate() function to compare which should work 

Ex: "UPDATE NEWUSERDATA SET statuskey = CASE WHEN(startdate <= CURDATE()) and (enddate > CURDATE() or enddate is null) then '1' WHEN (enddate <= CURDATE()) then '0' WHEN (startdate > CURDATE()) then '1' WHEN (startdate < CURDATE() and enddate < CURDATE()) then '0' end",

Thanks

Darshan

[Diptansu1]

We have tried this , and in the csv the start date and end date are both in YYYY-MM-DD format itself . the reason we have transformed it to make it YYYY-MM-DD HH:MM:SS is to match it with CURDATE() and btw every one of the query is running fine in ADMIN> SQL Analyzer but failing to trigger via SAV file. Can you please set up a call from Saviynt side to go through over this. We need to resolve this urgently.

[Darshanjain]

Hi @Diptansu1 

I did just test out a simple csv file upload with the same format as attached.

Used the below json as well

{ "ADDITIONALTABLES" : { "USERS" : "SELECT USERNAME,STARTDATE,ENDDATE FROM USERS" }, "COMPUTEDCOLUMNS" : ["STATUSKEY"], "PREPROCESSQUERIES" : ["UPDATE NEWUSERDATA SET STATUSKEY= CASE WHEN (enddate <= CURDATE()) then '0' WHEN (enddate > CURDATE()) then '1' end"]}

If you still face any issue, would suggest you to raise a FD ticket and our team will be able to check on this.

Thanks

Darshan

[Sampritha_r]

Hello @Diptansu1 

Are you passing the suffix 'th' in end date 15th Aug 2025?

Try with below preprocessor query assuming the date doesn't have any suffix.

Enddate: 15 Aug 2025 

or 

Enddate : 15 August 2025

"UPDATE NEWUSERDATA SET statuskey = (CASE WHEN ((DATE(STR_TO_DATE(ENDDATE, '%d %M %Y')) is null)) then '0' WHEN ((DATE(STR_TO_DATE(ENDDATE, '%d %M %Y')) is not null) and (DATE(STR_TO_DATE(ENDDATE, '%d %M %Y')) > DATE(CURDATE()))) then '1' WHEN ((DATE(STR_TO_DATE(ENDDATE, '%d %M %Y')) < DATE(CURDATE()))) then '0' end)"

This query is working for above sample end dates.  Hope this helps.

Thanks,

Sampritha R