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

Can End Date be set to a static value

Prithviraj
New Contributor II
New Contributor II

Hi Everyone,

We are trying to set enddate of users to a static value such as "31/12/2025" for some users.

We tried with the following query "UPDATE USERS SET enddate = DATE_FORMAT(STR_TO_DATE(31/12/2025, '%d/%m/%Y'),'%m/%d/%Y') where enddate is null or enddate='';

We are getting the error as "Inccorect Date Time value for function str_to_date". To which we updated the query to this

UPDATE USERS SET enddate = DATE_FORMAT(STR_TO_DATE(31/12/2025, '%d/%m/%YT%H:%i%s'),'%m/%d/%YT%H:%i%s') where enddate is null or enddate='';

But still getting the same error, please share us your thougths on the same.

Thanks,
Prithviraj

6 REPLIES 6

rushikeshvartak
All-Star
All-Star

UPDATE USERS
SET enddate = STR_TO_DATE('31/12/2025', '%d/%m/%Y %H:%i:%s')
WHERE enddate IS NULL OR enddate = '';


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

Hi @rushikeshvartak , still getting the same error.

Raghu
Honored Contributor
Honored Contributor

try below:

UPDATE USERS SET enddate = DATE_FORMAT(STR_TO_DATE('31/12/2025', '%d/%m/%Y'), '%m/%d/%Y') WHERE enddate IS NULL OR enddate = '';


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Prithviraj
New Contributor II
New Contributor II

Hi @Raghu , 

After trying with your suggestion I am getting error as "Data Truncation : Incorrect Date time value '12/31/2025' for 'ENDDATE' "

Raghu
Honored Contributor
Honored Contributor

try :

UPDATE USERS SET enddate = DATE_FORMAT(STR_TO_DATE('31/12/2025', '%d/%m/%Y'), '%Y-%m-%d %H:%i:%s') WHERE enddate IS NULL OR enddate = '';


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

Prithviraj
New Contributor II
New Contributor II

Thanks @Raghu , this worked for us.