Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    Try:

    1. Use a Temporary Variable to Parse the Date:
      Instead of directly comparing the parsed date to the original date string, you can use a temporary variable to hold the parsed date and then compare it.

    2. Use NULLIF and STR_TO_DATE Together:
      NULLIF can be used to return NULL if the date string does not match the expected format.

    My updated version:

    CREATE DEFINER=`root`@`localhost` TRIGGER `dirty_si_base_AFTER_INSERT` 
AFTER INSERT ON `dirty_si_base_AFTER_INSERT` FOR EACH ROW 
BEGIN
    DECLARE tempDate DATE;
    DECLARE fecha_valida_notificacion BOOLEAN;

    -- Attempt to parse the date
    SET tempDate = NULLIF(STR_TO_DATE(NEW.fecha_fallo_notificacion, '%Y-%m-%d'), NEW.fecha_fallo_notificacion);

    -- Check if the parsed date is NULL (indicating a parse failure)
    SET fecha_valida_notificacion = (tempDate IS NOT NULL);

    -- If the date is not valid, insert a record into the errores_carga table
    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;
    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search