user1181378 user1181378 - 3 months ago 19
MySQL Question

Date format regex testing in phpmyadmin

Date is stored in my MySql db field like that:

20160816
/* YearMonthDay*/

How do I search for entries by month?

Tried the following regex sql query to search for entries which has month (08) but no luck:

SELECT FROM posts WHERE posts.meta_key = 'start_date' AND (CAST(posts.meta_value AS CHAR) REGEXP '\\d{4}08\\d{2}')

Answer

First, make sure you run a regex on text data.

Then, note you can match a digit with [0-9] pattern, or [[:digit:]].

Thus, use

REGEXP '[0-9]{4}08[0-9]{2}'‌​

to get your entries containing four digits followed with 08 and then two digits.

Comments