Check if current date is between 2 dates in MySQL

Question

I have a contract table in my database and those contracts have a start date and an end date. How can I change their active state if the current date is after the end date?

I already figured out that I'll do it with a stored procedure that gets executed every day or something like that. My Problem is, I don't know, how I can check each row in the table. It seems like something extremely basic yet I can't think of any solution.

I found this Response and it looks very promising but I'm afraid I don't understand the way it's supposed to work.

I'd highly appreciate any and all pointers.

EDIT: I found the solution and my way of approaching the problem was way off.

To update all relevant rows, you write an UPDATE query and in the WHERE clause you specifiy the criteria of the rows you actually want to apply your update to — RiggsFolly, Oct 09 '19 at 08:34
Are `startdate` and `enddate` defined as DATE or DATETIME or TIMESTAMP datatypes? — RiggsFolly, Oct 09 '19 at 08:35
@RiggsFolly Thanks, that worked, my approach would have been so much more complicated. — Christian Gröber, Oct 09 '19 at 08:47
You can use `date()` function to type cast column value like that `WHERE CURDATE() > date(enddate)` — htmler, Oct 09 '19 at 08:48

score 0 · Accepted Answer · answered Oct 09 '19 at 08:48

0

Thanks to @RiggsFolly I found the solution:

UPDATE contract_conclusion SET is_active=0 WHERE CURDATE() > date_end_contract_conclusion;

answered Oct 09 '19 at 08:48

Christian Gröber

78
1
12

While this may solve your problem, it certainly doesn't satisfy the question posed in the title. – Strawberry Oct 09 '19 at 09:11
1

@Strawberry Why not? It just means that I approached the problem completely wrong, more from a PHP-Coder point of view without considering the database-oriented approach. So now I managed to solve it the way a problem like this is supposed to be solved, and not in the way I originally intended to. – Christian Gröber Oct 09 '19 at 09:17

Check if current date is between 2 dates in MySQL

1 Answers1