Check if date exists MySQL

Im validating an csv file that is being imported to the database via LOAD DATA LOCAL INFILE and im using a before insert trigger to achieve that (it is a dirty table, so when this process finishes i bulk correct data to another table). The problem is that i have a DATE field (Y-m-d) and if the script reads a non-existent date (ex: 2023-09-31, 2023-02-30) it throws:

Invalid datetime format: 1292 Truncated incorrect date value: '2023-09-31'.

I've trying to *catch *the error within my trigger but it is always showing the same error in my frontend.

heres part of my trigger.

CREATE DEFINER=`root`@`localhost` TRIGGER `dirty_si_base_AFTER_INSERT` AFTER INSERT ON `dirty_si_base_AFTER_INSERT` FOR EACH ROW BEGIN

[...]
DECLARE fecha_valida_notificacion BOOLEAN;



    [...]
    
     -- Verificamos si la fecha fallo es válida
     
   SET fecha_valida_notificacion = (
      STR_TO_DATE(NEW.fecha_fallo_notificacion, '%Y-%m-%d') IS NOT NULL AND 
    STR_TO_DATE(NEW.fecha_fallo_notificacion, '%Y-%m-%d') = NEW.fecha_fallo_notificacion
   );

    -- Si la fecha no es válida, ingresamos a la tabla de errores 
   IF NOT fecha_valida_notificacion THEN
        insert into errores_carga(con, mensaje, campo, archivo, created_at)
        values (cast(new.id  as char), 'La fecha no existe', 5, 'SI', now());
   END IF;
   
END

Im expecting to handle this error and insert a message in another table just like other errors.

Solution

You should also be aware that, for dates and the date portions of datetime values, STR_TO_DATE() checks (only) the individual year, month, and day of month values for validity. More precisely, this means that the year is checked to be sure that it is in the range 0-9999 inclusive, the month is checked to ensure that it is in the range 1-12 inclusive, and the day of month is checked to make sure that it is in the range 1-31 inclusive, but the server does not check the values in combination. For example, SELECT STR_TO_DATE('23-2-31', '%Y-%m-%d') returns 2023-02-31.

STR_TO_DATE

In short: STR_TO_DATE considers 2023-02-31 as well as 2023-09-31 to be valid.

But since (at least) your format is already valid, you can use DATE() instead, which will return NULL for 2023-02-31 and 2023-09-31. Even though they don't tell us much about in the documentation.

Another way:

CAST(NEW.fecha_fallo_notificacion as DATE) IS NOT NULL

should also work.