Decent Mysql datetime regular expression.

[webgovernor]

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)
^([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)

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.

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.

[webgovernor]

Oops, the section for the day should be:
(0?[1-9]|[1-2][0-9]|3[0-1]) // Correct
NOT
(0?[1-9]|[1-2][1-9]|3[0-1]) // Incorrect

Sorry about that.

[homemadedigital]

Hi there

I had a regex that validates a MySQL date including leap years, so I've run the "time" part of yours into the leapyear-enabled MySQL date regex. Here's a version where the "time" part of the datetime is required:

{^(((\d{4})(-)(0[13578]|10|12)(-)(0[1-9]|[12][0-9]|3[01]))|((\d{4})(-)(0[469]|11)(-)([0][1-9]|[12][0-9]|30))|((\d{4})(-)(02)(-)(0[1-9]|1[0-9]|2[0-8]))|(([02468][048]00)(-)(02)(-)(29))|(([13579][26]00)(-)(02)(-)(29))|(([0-9][0-9][0][48])(-)(02)(-)(29))|(([0-9][0-9][2468][048])(-)(02)(-)(29))|(([0-9][0-9][13579][26])(-)(02)(-)(29)))(\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9]))$}

And here's a version where it's optional:

{^(((\d{4})(-)(0[13578]|10|12)(-)(0[1-9]|[12][0-9]|3[01]))|((\d{4})(-)(0[469]|11)(-)([0][1-9]|[12][0-9]|30))|((\d{4})(-)(02)(-)(0[1-9]|1[0-9]|2[0-8]))|(([02468][048]00)(-)(02)(-)(29))|(([13579][26]00)(-)(02)(-)(29))|(([0-9][0-9][0][48])(-)(02)(-)(29))|(([0-9][0-9][2468][048])(-)(02)(-)(29))|(([0-9][0-9][13579][26])(-)(02)(-)(29)))(\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9]))?$}

[homemadedigital]

Hmmm. I forgot to enclose the regex in code tags so it's been part-converted into smilies. vBulletin should filter its input with a regex that spots regular expressions, and avoid putting smilies in them. Until that day:

Regex for leap-year enabled MySQL date/time validation (time required)
{^(((\d{4})(-)(0[13578]|10|12)(-)(0[1-9]|[12][0-9]|3[01]))|((\d{4})(-)(0[469]|11)(-)([0][1-9]|[12][0-9]|30))|((\d{4})(-)(02)(-)(0[1-9]|1[0-9]|2[0-8]))|(([02468][048]00)(-)(02)(-)(29))|(([13579][26]00)(-)(02)(-)(29))|(([0-9][0-9][0][48])(-)(02)(-)(29))|(([0-9][0-9][2468][048])(-)(02)(-)(29))|(([0-9][0-9][13579][26])(-)(02)(-)(29)))(\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9]))$}

Regex for leap-year enabled MySQL date/time validation (time optional)
{^(((\d{4})(-)(0[13578]|10|12)(-)(0[1-9]|[12][0-9]|3[01]))|((\d{4})(-)(0[469]|11)(-)([0][1-9]|[12][0-9]|30))|((\d{4})(-)(02)(-)(0[1-9]|1[0-9]|2[0-8]))|(([02468][048]00)(-)(02)(-)(29))|(([13579][26]00)(-)(02)(-)(29))|(([0-9][0-9][0][48])(-)(02)(-)(29))|(([0-9][0-9][2468][048])(-)(02)(-)(29))|(([0-9][0-9][13579][26])(-)(02)(-)(29)))(\s([0-1][0-9]|2[0-4]):([0-5][0-9]):([0-5][0-9]))?$}

[emmim44]

How can you tweak your regex to confirm leap-year with this format [mm-dd-yyyy hh:mm:ssAM/PM]?