We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Date conversion from DD/MM/yyyy to MM/DD/YYYY in preprocessor

Abdul_Gaffar
New Contributor II
New Contributor II

Hi ,

we have a usecase where the data from source is coming in DD/MM/YYYY format and convert this into MM/DD/YYYY format 

While trying this in preprocessor query we are facing synatx error 

Preprocessor query:

{ "ADDITIONALTABLES": {"USERS": "SELECT USERKEY FROM USERS"},"COMPUTEDCOLUMNS": ["customproperty20"],"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='DEVusers'","UPDATE NEWUSERDATA SET startdate = select DATE_FORMAT(NEWUSERDATA.startdate, '%m/%d/%Y') from currentusers where currentusers.systemusername=newuserdata.systemusername","UPDATE NEWUSERDATA SET enddate = select DATE_FORMAT(NEWUSERDATA.enddate, '%m/%d/%Y') from currentusers where currentusers.systemusername=newuserdata.systemusername"]}

 

please suggest what shall be the correct syntax

Regards,

Abdul Gaffar

 

10 REPLIES 10

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Abdul_Gaffar,

Please try using this 
UPDATE NEWUSERDATA SET startdate = select DATE_FORMAT(startdate, '%m/%d/%Y')

Thanks

Abdul_Gaffar
New Contributor II
New Contributor II

Hi @sudeshjaiswal ,

I tried as specified above still says syntax error

 

Regards,

Abdul Gaffar

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Abdul_Gaffar,

I can see all possible checks are done. Next Step would be  You need to see the logs when error is observed to know what is missing.

Thanks.

Hi @sudeshjaiswal ,

The following is the error 

 You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server
version for the right syntax to use near 'select
DATE_FORMAT(startdate, '%m/%d/%Y')' at line 1

Preprocessor query:

UPDATE NEWUSERDATA SET startdate = select DATE_FORMAT(startdate, '%m/%d/%Y')

Regards

Abdul gaffar

@Abdul_Gaffar : Try below

UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(NU.startdate, '%m/%d/%Y')

 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Abdul_Gaffar,

Can you please try with the below query,

UPDATE NEWUSERDATA SET startdate = DATE_FORMAT(startdate, '%m/%d/%Y')

Thanks

Abdul_Gaffar
New Contributor II
New Contributor II

Hi @sk ,

I have tried as per your specification 

Facing error as " Error while processing
data: Data truncation: Incorrect datetime value:
'03-04-2021'"

Any suggestions to replicate

Regards

Abdul Gaffar

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @Abdul_Gaffar,

Can you please share the full json with the above changes.

Thanks.

AbdulGaffar
New Contributor III
New Contributor III

Hi @sudeshjaiswal 

Below is the modify user data json :

{"ADDITIONALTABLES": {"USERS": "SELECT USERKEY,startdate,enddate,systemusername FROM USERS"},"COMPUTEDCOLUMNS": ["customproperty20","startdate","enddate"],"PREPROCESSQUERIES": ["UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 ='DEVusers'","UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(NU.startdate, '%m/%d/%Y')","UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(NU.enddate, '%m/%d/%Y')"]}

 

Regards

Abdul Gaffar

sudeshjaiswal
Saviynt Employee
Saviynt Employee

Hello @AbdulGaffar,

First, in your userimport JSON, map the values for 'startdate' and 'enddate' that you are fetching from your data source into custom properties, namely 'customproperty1' and 'customproperty2'. These custom properties will store the date values in the DD/MM/YYYY format. Then, in the preprocessor, set 'startdate' and 'enddate' as shown below:


{
"ADDITIONALTABLES": {
"USERS": "SELECT USERKEY, customproperty1 AS startdate, customproperty2 AS enddate, systemusername FROM USERS"
},
"COMPUTEDCOLUMNS": ["customproperty20", "startdate", "enddate"],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET CUSTOMPROPERTY20 = 'DEVusers'",
"UPDATE NEWUSERDATA NU SET NU.startdate = DATE_FORMAT(STR_TO_DATE(NU.customproperty1, '%d/%m/%Y'), '%Y-%m-%d')",
"UPDATE NEWUSERDATA NU SET NU.enddate = DATE_FORMAT(STR_TO_DATE(NU.customproperty2, '%d/%m/%Y'), '%Y-%m-%d')"
]
}
```

Please note that setting 'startdate' and 'enddate' will always be in the "yyyy-mm-dd" format, as these  are in datetime type columns. Other formats will not be accepted.

Thanks