Is there any better way i can update this records in "mysql database"-CodePudding

Please, am new in MYSQL Database and Python. I have created a "leave management system" which is a desktop application using python, Tkinter and MYSQL. I wrote an SQL code that will compare "current date" to the "leave end date". Sure that whenever the "current date" is greater than the "leave end date", the "leave status" should be update to "Expired" else it should be update to " In Progress". The problem am having now is that the code can only work for the current month. example: if Leave end date = 25/09/2021,the "leave status" will be updated to "In Progress" which is the desired result I expected. But if the leave end date = 09/10/2021, the leave status is update to expired, I observed that once the "leave end date" month is greater than the "current date" month the "leave status" will be udated to "Expired" which is wrong. Below is the code:

 my_cursor.execute(''' UPDATE leaves
                        SET leave_status =
                           CASE
                              WHEN DATE_FORMAT(current_date(), '%d/%m/%y') > leave_end_date THEN 'EXPIRED'
                              ELSE 'IN PROGRESS'
                        END
                                 ''')

Note: Please I am using calendar picker for the date entry that is why I used this Date_format() function. I will be very grateful if someone could help me out.

CodePudding user response：

You can't use d/m/y format for comparisons. It gives most significance to the day of month, not the year and month.

Instead of formatting the current date, parse the value of leave_end_date to a date and compare that.

CASE WHEN CURRENT_DATE() > STR_TO_DATE(leave_end_date, '%d/%m/%Y')

Ideally you shouldn't store formatted dates in the database, the datatype of leave_end_date should be DATE.