Hi,

is there any way I can dynamically limit the values that are accepted in a date field?

For example, not allow any values in the future, or not allow values that are more than 10 days back from today, etc.

Thank you

Thanks, but this is not an option for us at the moment.

I've found a solution using MySQL triggers.

For anyone looking for something like this, here's the code:

Say you have a table 'table' in database 'db1' with a column 'date'.

DROP TRIGGER IF EXISTS chk_date_upd;
delimiter //
use db1 //
create trigger chk_date_upd before update on db1.table
for each row
  begin
    -- add 2000 years if the date is added like this: 10/11/17
    if  YEAR(new.date) < 100 then
        SET new.date= DATE_ADD(new.date, INTERVAL 2000 YEAR);
    end if;
    -- refuse date which is too far in the past
    if  new.date<= DATE_ADD(CURDATE(), INTERVAL -6 MONTH) then
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Date value cannot be more than six months in the past';
    end if;
    -- refuse date which is too far in the future
    if  new.date>= DATE_ADD(CURDATE(), INTERVAL 2 MONTH) then
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Date value cannot be more than two months in the future';
    end if;
  end;
//
delimiter ;

Add another trigger on insert:

DROP TRIGGER IF EXISTS chk_date_ins;
delimiter //
use db1 //
create trigger chk_date_ins before insert on db1.table
for each row
  ...
//
delimiter ;

The triggers will be called before insert/update and adjusts the date or refuse it and display the custom error message to the user.

Ticket can be closed.