MySQL Cookbook

Using Patterns to Match Dates or Times

Problem

You need to make sure a string looks like a date or time.

Solution

Use a pattern that matches the type of temporal value you expect. Be sure to consider issues such as how strict to be about delimiters between subparts and the lengths of the subparts.

Discussion

Dates are a validation headache because they come in so many formats. Pattern tests are extremely useful for weeding out illegal values, but often insufficient for full verification: a date might have a number where you expect a month, but if the number is 13, the date isn’t valid. This section introduces some patterns that match a few common date formats. Recipe 10.31 revisits this topic in more detail and discusses how to combine pattern tests with content verification.

To require values to be dates in ISO (CCYY-MM-DD) format, use this pattern:

/^\d{4}-\d{2}-\d{2}$/

The pattern requires - as the delimiter between date parts. To allow either - or / as the delimiter, use a character class between the numeric parts (the slashes are escaped with a backslash to prevent them from being interpreted as the end of the pattern constructor):

/^\d{4}[-\/]\d{2}[-\/]\d{2}$/

Or you can use a different delimiter around the pattern and avoid the backslashes:

m|^\d{4}[-/]\d{2}[-/]\d{2}$|

To allow any non-digit delimiter (which corresponds to how MySQL operates when it interprets strings as dates), use this pattern:

/^\d{4}\D\d{2}\D\d{2}$/

If you don’t require the full number of digits in each part ...