Verifying string values with multiple date format is a date

{question}

How do I verify if string values with multiple date format is a date?

{question}

{answer}

For values that you know the formatting of the data, this operation is simple, and you can use the STR_TO_DATE.

singlestore> SELECT *
-> FROM tst01 
-> WHERE STR_TO_DATE(description, '%Y-%m-%d') IS NOT NULL;
+----+-------------+------+
| id | description | data |
+----+-------------+------+
| 4 | 2020-02-29 | NULL |
| 5 | 2021-02-29 | NULL |
| 3 | 2020-02-28 | NULL |
| 2 | 2020-02-31 | NULL |
+----+-------------+------+
4 rows in set (0.05 sec)

This will return every value without confirming if it's a valid date or not. To perform both validations, we can use the DATE function.

This is enough if you are using only one type of date format, but if we want to confirm multiple date formats?

We can use the implementation of MPSQL language that allows us to create user-defined functions.

This is an example of a UDF for some date formats:

DELIMITER //
CREATE OR REPLACE FUNCTION isdate(input varchar(20)) RETURNS bool AS
BEGIN
  IF DATE(STR_TO_DATE(input, '%Y-%m-%d')) IS NOT NULL THEN 
    RETURN TRUE;
  ELSIF DATE(STR_TO_DATE(input, '%m/%d/Y')) IS NOT NULL THEN
    RETURN TRUE;
  ELSIF DATE(STR_TO_DATE(input, '%m-%d-%Y')) IS NOT NULL THEN
    RETURN TRUE;
  ELSIF DATE(STR_TO_DATE(input, '%Y%m%d')) IS NOT NULL THEN
    RETURN TRUE;
  ELSIF DATE(STR_TO_DATE(input, '%m-%d-%y')) IS NOT NULL THEN
    RETURN TRUE;
  END IF;
  RETURN FALSE;
END //
DELIMITER ;

Then you need to call the UDF like the example below:

mysql> select isdate('2020-08-31');
+----------------------+
| isdate('2020-08-31') |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.26 sec)

mysql> select isdate('2020-08-32');
+----------------------+
| isdate('2020-08-32') |
+----------------------+
| 0 |
+----------------------+
1 row in set (0.24 sec)

{answer}