CODEWITHSUNDEEP
validationmariadb
layal
layal

Reputation: 55

how to check for invalid dates in MariaDB

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 ?

Upvotes: 1

Views: 1113

Answers (1)

Georg Richter
Georg Richter

Reputation: 7476

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)

Upvotes: 3

Related Questions