CODEWITHSUNDEEP
mysqldatedatetime
R R
R R

Reputation: 2956

Convert any valid date format to date in mysql

How can we check if a string is a valid date without specifying the format in MySQL.A user may enter below formats;

21-08-2013
21/08/2013
2013/08/21
21jan2018

I don't know the format in advance which a user may enter. I tried :

Select str_to_date() but here I need to specify format which I don't want. I just want to check if a string is valid date without specifying the format.

Please suggest. Thanks

Upvotes: 1

Views: 39

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

All four of the date strings you showed us in the question can be converted to a MySQL date using STR_TO_DATE with an appropriate format mask. So, you should be able to simply check for these date patterns, if all you want is to know if you could convert them to dates:

SELECT *
FROM yourTable
WHERE
    col REGEXP '[0-9]{2}-[0-9]{2}-[0-9]{4}' OR
    col REGEXP '[0-9]{2}/[0-9]{2}/[0-9]{4}' OR
    col REGEXP '[0-9]{4}/[0-9]{2}/[0-9]{2}' OR
    col REGEXP '[0-9]{2}[A-Z]{3}[0-9]{4}';

Upvotes: 1

Related Questions