Check if date is valid in Mysql database

Question

Good afternoon programmers, I have a problem in the trial where I need to print all the dates of the month, I created a loop until the day 31, but there is month that is less days that gives error, as check in the trial if the date is valid?

DELIMITER $$

CREATE PROCEDURE VisaoEvolucaoSemanal(dataIn date)
BEGIN
DECLARE dia decimal(2);
DECLARE mes varchar(10);
DECLARE ano varchar(10);
DECLARE media decimal(10,2);
DECLARE dataBusca date;
DECLARE datasaida date;

set dia = 00;
set mes = month (dataIn);
set ano = year(dataIn);

loop_data: LOOP
    SET dia = dia + 01;

    set datasaida = CONCAT(ano,'-',mes,'-',dia);

    select datasaida;

    IF dia > 31 THEN
        LEAVE loop_data;
    END IF;
END LOOP loop_data;

END$$
DELIMITER ;

Answer 1

You can use the function WEEK to check if a date string is valid. I’ve mounted an example below.

Schema (Mysql v5.7)

CREATE TABLE dias (
  descricao VARCHAR(10)
);

INSERT INTO dias(descricao)
          VALUES('2019-02-28'),
                ('2019-02-29');

---

Query

SELECT d.*,
       CASE 
         WHEN WEEK(d.descricao) IS NULL THEN 'INVÁLIDA'
         ELSE 'VÁLIDA'
       END AS data_valida
  FROM dias d;

---

Upshot

| descricao  | data_valida |
| ---------- | ----------- |
| 2019-02-28 | VÁLIDA      |
| 2019-02-29 | INVÁLIDA    |

---

See working on DB Fiddle