how to check for invalid dates in MariaDB-CodePudding

I want to check the validity of data in a datetime column in my table. I have tried to use ISDATE() function but it's not supported in Maria DB , what are other options to check the validity of datetime column in Maria DB ?

CodePudding user response：

Unless sql mode "ALLOW_INVALID_DATES" is enabled, MariaDB checks by default if the datetime value is valid or not. (See DATETIME in MariaDB Documentation

Since MariaDB doesn't provide a validation function (like e.g. SQL Server) you need to use a conversion function which returns NULL if the datetime value is not correct:

MariaDB [test]> select id, dt from mytable;
 ------ --------------------- 
| id   | dt                  |
 ------ --------------------- 
|    2 | 2001-02-31 00:00:00 |
|    3 | 2002-01-01 13:27:00 |
|    4 | 2020-12-01 00:00:00 |
|    5 | 2022-01-11 16:59:04 |
 ------ --------------------- 
4 rows in set (0.001 sec)

MariaDB [test]> select id,dt from mytable where dayname(cast(dt as char)) is NULL;
 ------ --------------------- 
| id   | dt                  |
 ------ --------------------- 
|    2 | 2001-02-31 00:00:00 |
 ------ --------------------- 
1 row in set, 1 warning (0.001 sec)