Menu
I’ve spent a lot of time trying to find a regular expression matching valid boundaries for MySQL datetimes before I knew anything of regular expressions. Since I had to write my own, I thought I’d share it with those who don’t feel like spending hours picking through very poor alternatives. Most of the examples that I found didn’t check for illegal boundaries, such as 99 days in a month, or more than 12 months in a year.
Mysql Datetime Regular Expression (YYYY-MM-DD HH:MM:SS)
[CODE]^([1-3][0-9]{3,3})-(0?[1-9]|1[0-2])-(0?[1-9]|[1-2][1-9]|3[0-1])s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9])$
This is effectively the same as the readable expression:
(1000-3999)-(1-12)-(1-31) (00-24)?00-59)?00-59)
[B]Broken down:
Year: ([1-3][0-9]{3,3}) Matches 1000 to 3999, easily changed.
Month: (0?[1-9]|1[0-2]) Matches 1 to 12
Day: (0?[1-9]|[1-2][1-9]|3[0-1]) Mathes 1 to 31
Hour: ([0-1][0-9]|2[0-4]) Matches 00 to 24
Minute: ([0-5][0-9]) Matches 00 to 59
Second: ([0-5][0-9]) Same as above.
[B]Notes:
The “?” allows for the preceding digit to be optional, ie: “2008-1-22” and “2008-01-22” are both valid.
The “^“ denies input before the year, so ” 2008″ or “x2008” is invalid.
The “$” works to deny ending input.