MySQL Query Group By Date

Question

I have tried various recommendations based off of other posts with no avail.

I have a database scheme of records with a Created_Date Key, and Value would be 01/01/2017

I am trying to query the database records to give a returned count of How many records per month and which month those fall in line with.

With the following

SELECT SQL_NO_CACHE MONTH(`Created_Date`), COUNT(*)
FROM `CRM_Leads`
GROUP BY MONTH(`Created_Date`)

I return

MONTH(`Created_Date`)   COUNT(*)    
          NULL            872

I have also tried almost all the variations on the following post

Count records for every month in a year

Any help would be appreciated.

You will need to convert them to date values before using them with the MONTH function; MySQL provides STR_TO_DATE() for that; I'd recommend the long term solution as converting the column to a proper date if possible though. — Uueerdo
– Uueerdo, Commented Nov 24, 2017 at 21:59

ScaisEdge · Accepted Answer · 2017-11-24 22:13:59Z

3

assuming your created_date is a string of format ('dd-mm-yyyy') the you should convert as date with str_to_date

SELECT SQL_NO_CACHE MONTH(str_to_date(`Created_Date`, '%d/%m/%Y')), COUNT(*)
FROM `CRM_Leads`
GROUP BY MONTH(str_to_date(`Created_Date`, '%d/%m/%Y'))

edited Nov 24, 2017 at 22:13

answered Nov 24, 2017 at 21:59

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

LeviZoesch Over a year ago

returns MONTH(str_to_date(Created_Date, '%y-%m-%d')) COUNT(*) NULL 872

LeviZoesch Over a year ago

scheme 17 Created_Date varchar(15) latin1_swedish_ci No None

LeviZoesch Over a year ago

Date is 01/01/2017

Paul Maxwell · Accepted Answer · 2017-11-24 22:46:20Z

For as long as you store date/time information as strings, you will have great difficulty using any date/time specific functions and features. If you are getting NULL from MONTH(str_to_date(Created_Date, '%d/%m/%Y')) then the str_to_date isn't converting the strings to dates and the most likely reason for this is the d m y "pattern" is not corrrect.

All you have old us about your "strings that might be dates" is that one of them looks like this: 01/01/2017. Now that could be DD/MM/YYYY or MM/DD/YYYY and we simply cannot tell which one is correct from the single value you have chosen to share with us. Look for any day value greater then 12 in your data e.g. 17/01/2017 ==> DD/MM/YYYY or 01/17/2017 ==> MM/DD/YYYY

Once you have made the choice of which pattern your "strings that might be dates" follow; apply that pattern in the str_to_date() function. You migh want to try a few different patterns to get the best one (and these are just 3 of many you could try):

# which pattern is best for you?
SELECT Created_Date
     , str_to_date(`Created_Date`, '%d/%m/%Y') "d/m/y"
     , str_to_date(`Created_Date`, '%m/%d/%Y') "m/d/y"
     , str_to_date(`Created_Date`, '%Y-%m-%d') "y-m-d"
FROM `CRM_Leads`

You will not have success with your group by query until you choose the most appropriate d m y pattern to apply in teh str_to_date function. Note here that you might also have a variety of patterns in your data, in which case you have an even bigger problem to solve.

Once you have made the choice of which pattern your "strings that might be dates" follow; apply that pattern in the str_to_date() function and ONLY THEN your group by query will work.

Collectives™ on Stack Overflow

MySQL Query Group By Date

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related