Password creation preprocessor is not working with date functions

[KME]

Hi Folks,

We have a requirement to generate password and place it in User's customproperty 65
Pre-requisites:

1. User's customproperty6 should be 'A'
2. User's customproperty65 should be null
3.  User's startdate should equals to the current date

Password generation rule:  first letter of firstname+first letter of lastname+User's cp3+##xx$. We designed the pre-processor as below:

Update newuserdata set customproperty65 = (Case when (Customproperty6 ='A' and customproperty65 is null and (str_to_date(startdate, '%Y-%m-%d') = CURRENT_DATE()) )then CONACT(lower(SUBSTRING(firstname,1,1,)),lower(substring(lastname,1,1)),customproperty3,'##xx$') else customproperty65 end)

The above one is not working. but if we remove date comparison in the pre-processor like below, password is generating.

Update newuserdata set customproperty65 = (Case when (Customproperty6 ='A' and customproperty65 is null )then CONACT(lower(SUBSTRING(firstname,1,1,)),lower(substring(lastname,1,1)),customproperty3,'##xx$') else customproperty65 end)

The date comparison is working fine in data-analyser but it is not working in pre-processor.
ref query used in data analyser :

Select createdate, startdate,username from users where str_to_date(startdate, '%Y-%m-%d') = CURRENT_DATE()

Could anyone please provide any ideas/inputs on this?
Thanks in advance!!

[naveenss]

Hi @KME 

May I know the use case behind this? Ideally its not a best practice to store password values as a clear text in the user attributes. 

[KME]

Hi @naveenss ,

as the process is in initial stage, we just placing the pwd in user attributes and we won't place going forward.

Could you please let us know if we can use any other function to check the startdate = current date in pre-processor.
Thanks in advance!!

[naveenss]

Hi @KME 

Please try below condition

date(startdate)=curdate()

[KME]

@naveenss -- no luck with date(startdate)=curdate().
In logs, it is showing that incorrect datetime value 
Note:

startdate format in saviynt is "May 15, 2024 00:00:00" and curdate format is "2024-05-16".

[rushikeshvartak]

UPDATE newuserdata
SET customproperty65 = (
CASE
WHEN customproperty6 = 'A'
AND customproperty65 IS NULL
AND startdate = CURRENT_DATE()
THEN CONCAT(
LOWER(SUBSTRING(firstname, 1, 1)),
LOWER(SUBSTRING(lastname, 1, 1)),
customproperty3,
'##xx$'
)
ELSE customproperty65
END
)