Last Updated: September 26, 2019
·
11.69K
· euantor

Regex to check for valid MySQL datetime format

I recently found my self needing to validate a date/time input and decided to use regex to accomplish the task. Since the value is to be inserted into a MySQL column of type datetime, the below regex works flawlessly:

^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9])(?:( [0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$

The regular expression will also accept either a pure date (2012-09-28) or a date + time (2012-09-28 21:00:00).

1 Response
Add your response

For newer versions of MySQL or MariaDB, one might use milliseconds and microseconds precision for datetime values (e.g: "2019-02-03 12:44:35.434344").
So, an updated regex to accept these values would be:
^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9])(?:( [0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?(.[0-9]{1,6})?$

over 1 year ago ·