• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

How to pull MySQL records based on Date in MM/YYYY format

Hi all.

I have an Excel 2007 file that pulls data from a MySQL database.

The fields it pulls are: Name, Count(Type) and the criteria is the CreateDate field which is a datetime datatype which is stored in this format: YYYY-MM-DD HH:MM:SS.

My Excel has 4 parameters: Month1, Year1, Month2 and Year2 (cells B1, D1, B2 and D2).

So the query would be something like:

"SELECT Name,Count(Type)  FROM DataEntry INNER JOIN Users On DataEntry.UserID = Users.UserID WHERE Type = 1 AND CreateDate >= '" & CONCATENATE(B1, "/1/", D1) & "' AND CreateDate <= '" & CONCATENATE(B2, "/1/", D2) & "' GROUP BY Name"

Open in new window

As you can see I am stuck because I don't know how to do the last day of the month since some months end on 31, 30, 28 or 29 if it's a leap year. So I thought maybe I can do it if I format the CreateDate field as MM/YYYY and then concatenate my Excel cells as D1/B1 and D2/B2.

Any help would be appreciated.

Thank you in advance!
2 Solutions
Add 1 to the month, then subtract 1 from the day to get the end of the month

DATEADD("DAY",-1,DATEADD("MONTH",1,"01/" & MM & "/" & YYYY"))
Chris StanyonCommented:
In MySQL there is a function called LAST_DAY which, not surprisingly, returns the Last Day for a given month. Also, if this is mySQL then use CONACT_WS (concatenate with Separator). Finally, use BETWEEN instead of >= and <= - it makes more sense :)

//select data between 1st October 2013 and the last day of November 2013
SELECT column1, column2
FROM someTable
WHERE yourDate
BETWEEN CONCAT_WS('/', '2013', '10', '01') AND LAST_DAY(CONCAT_WS('/', '2013', '11', '01'));

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now