I have a table of around 6000 records with a date column amongst other columns which represent the deadline for a query. I need to compare the date in the column to todays date which I understand is done something like:
SELECT DATEDIFF(DATE_TO_COMPARE, CURDATE());
field 1, field2, field 3, date_to_compare, field 4, field 5, difference_in_days
| | | | 2016-04-20 | | | |
| | | | 2016-04-25 | | | |
| | | | 2016-04-22 | | | |
| | | | 2016-04-27 | | | |
| | | | 2016-04-29 | | | |
Sonds like you want to do an update?
UPDATE table_name SET difference_in_days = DATEDIFF(date_to_compare, CURDATE());
This will update every record in the table to the diff of the current date.
However, this will require you running the update every day, if you want that column to maintain relevance.
If you're not querying this a lot, you may be better off using a view, which will update real-time every time you query it.
CREATE VIEW diff_view_name AS SELECT *, DATEDIFF(date_to_compare, CURDATE()) AS difference_in_days FROM table_name;
Then you could query it using:
SELECT * FROM diff_view_name;