Click HERE to see how Saviynt Intelligence is transforming the industry. |
04/10/2024 09:53 PM
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
Solved! Go to Solution.
04/10/2024 10:16 PM - edited 04/10/2024 10:16 PM
UPDATE USERS
SET enddate = STR_TO_DATE('31/12/2025', '%d/%m/%Y %H:%i:%s')
WHERE enddate IS NULL OR enddate = '';
04/10/2024 10:18 PM
Hi @rushikeshvartak , still getting the same error.
04/10/2024 10:27 PM
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 = '';
04/10/2024 10:31 PM
Hi @Raghu ,
After trying with your suggestion I am getting error as "Data Truncation : Incorrect Date time value '12/31/2025' for 'ENDDATE' "
04/10/2024 10:35 PM
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 = '';
04/10/2024 10:38 PM
Thanks @Raghu , this worked for us.