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

[Abdul_Gaffar]

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

[sudeshjaiswal]

Hello @Abdul_Gaffar,

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

Thanks

[Abdul_Gaffar]

Hi @sudeshjaiswal ,

I tried as specified above still says syntax error

Regards,

Abdul Gaffar

[sudeshjaiswal]

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.

[Abdul_Gaffar]

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

[sk]

@Abdul_Gaffar : Try below

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

[sudeshjaiswal]

Hello @Abdul_Gaffar,

Can you please try with the below query,

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

Thanks

[Abdul_Gaffar]

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]

Hello @Abdul_Gaffar,

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

Thanks.

[AbdulGaffar · Accepted solution]

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]

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