重新计算存储为varchar类型的日期

Question

I have a table that contains expiry dates for software modules. They are stored as type varchar(32). The database also stores dates one month prior to expiry and one day prior to expiry. When a license is approaching expiry the client is notified by message stack when they go into site admin.

What i want is to be able to edit the expiry date so that additional months or even years can be added to a clients license. The issue is that it also needs to update the other dates relative to the new expiry or they will get warnings when they shouldn't.

So field 'edate' contains a date stored as 25-10-2015, 'wdate' contains 25-09-2014 and 'fwdate' contains 24-10-2015

How do you take the date stored in the db and make it usable with code as seen below which originally calculated the dates.

$todays_date = strtotime("now");
$delta_eleven = strtotime("+11 months");
$delta_364 = strtotime("+1 year -1 day");
$delta_year = strtotime("+1 year");

$idate = date("d-m-Y", $todays_date);
$wdate= date("d-m-Y", $delta_eleven);
$fwdate = date("d-m-Y", $delta_364);
$edate = date("d-m-Y", $delta_year);

Answer 1

On database side you can use STR_TO_DATE(). Explanation of the second parameter can be found here. In your case

SELECT STR_TO_DATE(your_date_column, '%d-%m-%Y') FROM your_table;

To add or subtract something you can use

SELECT STR_TO_DATE(your_date_column, '%d-%m-%Y') + INTERVAL 11 MONTH
FROM your_table;

or

SELECT STR_TO_DATE(your_date_column, '%d-%m-%Y') + INTERVAL 1 YEAR - INTERVAL 1 DAY
FROM your_table;

It would be best if you added a column with the appropriate datatype (date, datetime or timestamp), update the new column with the str_to_date() function and then drop the old varchar column containing your dates. This makes work much easier and you can use indexes on the column if necessary.