Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Date format in preprocessor

HarishG
Regular Contributor
Regular Contributor

Hi,

We are getting the startdate in the import file in this format(20140825000000Z)

I am trying to get the first 8 digits and convert to format with saviynt accepts using below preprocessor query. But it doesnt work. Any suggestions here?

{ "ADDITIONALTABLES": { "USERS": "SELECT username FROM USERS" }, "COMPUTEDCOLUMNS": [ "startdate" ], "PREPROCESSQUERIES": [ "Update NEWUSERDATA set startdate=CASE WHEN (startdate is not null or startdate != '') THEN DATE_FORMAT(SELECT SUBSTRING(startdate, 1,8),'%Y%m%d') ELSE NULL END" ] }

 

 

Best regards

Harish

2 REPLIES 2

PremMahadikar
All-Star
All-Star

Hi @HarishG ,

The below script is working for me. Please try...

{ 
"ADDITIONALTABLES": { 
"USERS": "SELECT username FROM USERS" 
}, 
"COMPUTEDCOLUMNS": [ 
"startdate" 
], 
"PREPROCESSQUERIES": [ 
"Update NEWUSERDATA set startdate = CASE WHEN startdate is not null or startdate != '' THEN DATE_FORMAT(LEFT(startdate,8),'%d-%m-%Y') ELSE NULL END" 
] 
}

 FYI:

My csv file: 
usernamestartdate
xyztst2014082500000Z

 

Startdate updated in SSM:

PremMahadikar_0-1717799267793.png

If this helps your question, please consider selecting Accept As Solution and hit Kudos

 

rushikeshvartak
All-Star
All-Star

{
"ADDITIONALTABLES": {
"USERS": "SELECT username FROM USERS"
},
"COMPUTEDCOLUMNS": [
"startdate"
],
"PREPROCESSQUERIES": [
"UPDATE NEWUSERDATA SET startdate = CASE WHEN startdate IS NOT NULL AND startdate != '' THEN DATE_FORMAT(STR_TO_DATE(SUBSTRING(startdate, 1, 8), '%Y%m%d'), '%Y-%m-%d') ELSE NULL END"
]
}


Regards,
Rushikesh Vartak
If you find this response useful, kindly consider selecting 'Accept As Solution' and clicking on the 'Kudos' button.