Count and select a separate date format format from a table column.

Question

In my table, I have several types of date format in a table column, for example.

20 May 1984
20.03.2004
20/June/2005
20 August 1925

So, how to get the different date formats that are used in my table.

The output should be as follows: -

column | count
-------|------------
 d M y |   15
 d.m.y |   25
 d/M/y |   12
 d F y |   10

Answer 1

You can use STR_TO_DATEto solve this problem. STR_TO_DATEgives NULLif the value cannot be read in the specified format. Thus, with SUMand CASE WHENyou can calculate the found dates with the specified format:

SELECT 
    SUM(CASE WHEN NOT STR_TO_DATE(colVal, '%d %M %Y') IS NULL THEN 1 ELSE 0 END) AS 'd M y',
    SUM(CASE WHEN NOT STR_TO_DATE(colVal, '%d.%m.%Y') IS NULL THEN 1 ELSE 0 END) AS 'd.m.y',
    SUM(CASE WHEN NOT STR_TO_DATE(colVal, '%d/%M/%Y') IS NULL THEN 1 ELSE 0 END) AS 'd/M/y'
FROM table_name

demo: http://sqlfiddle.com/#!9/bc0e9/2/0

STR_TO_DATE, . MySQL : https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

. MySQL , , .

Answer 2

, :

SELECT 

    IF( cdate REGEXP (' ') , 'd M Y' , 
       IF(cdate REGEXP ('.') , 'd.m.Y' ,
          IF(cdate REGEXP ('/') , 'd/M/Y' ,'1')
       )
    ) AS format_type  , count(id) as total 


FROM datawithdate GROUP BY format_type

cdate - , datawithdate - .

demo: http://sqlfiddle.com/#!9/50809/1

. 1 , ,