Count number of Mon, Tue, etc... between a date range

I am trying to pull out the number of Mondays, Tuesdays, ..., Sundays inbetween two date ranges in a mysql database (ex. 3/10/04 and 4/10/04).

Each day count would need to be a seperate select statement.

As in all my questions, I would love to keep it in a MySQL statement if possible :)

Thanks for the help

Who is Participating?
madwaxConnect With a Mentor Commented:

DATEFORMAT must be replaced with DATE_FORMAT

and the following query is tested and works:

SUM(IF(DATE_FORMAT(myDateField,"%a")="Mon",1,0)) AS nbrOfMondays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Tue",1,0)) AS nbrOfTuesdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Wed",1,0)) AS nbrOfWednesdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Thu",1,0)) AS nbrOfThursdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Fri",1,0)) AS nbrOfFridays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Sat",1,0)) AS nbrOfSaturdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Sun",1,0)) AS nbrOfSundays
myDateField >= "2004-01-01" AND myDateField <= "2004-04-01"

You will probably have to change the formatting of your datestring so it is compatible with MySQL:s (swedish! :) ) notation "YYYY-MM-DD"

//madwax (of sweden :)

P.S. I didn't get the thing with separate queries... you may take one day at the time (at a separate query) but I don't really see the point... D.S.
I think that the following could work, I can't test it until later tonight....


SUM(IF(DATEFORMAT(myDateField,"%a")="Mon",1,0)) AS nbrOfMondays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Tue",1,0)) AS nbrOfTuesdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Wed",1,0)) AS nbrOfWednesdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Thu",1,0)) AS nbrOfThursdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Fri",1,0)) AS nbrOfFridays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Sat",1,0)) AS nbrOfSaturdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Sun",1,0)) AS nbrOfSundays
myDateField >= "3/10/04" AND myDateField <= "01/01/04"
keeper3Author Commented:
That will work as done, though I should of mentioned on thing.  In the database where the dates are stored, each record has a start_date and a end_date, and I am looking to analyze all the dates in between.  

bill_id    start_date    end_date
1         2003-02-12    2003-03-13
2         2003-03-13    2003-04-15

I'm having trouble using your statement b/c I don't have a list of dates to select through (myDateField), just a start and end date.  Is there a way around this?

That isn't possible without using either a written function compiled into MySQL OR having a programming language take care of the post and running the query for every tuple. The latter is the easiest to implement, but not very good if it is going to be run many times since you will have several queries to the db each time.

read more on mysql create functions:

keeper3Author Commented:
Well I succumed and imported a date_list field with every date from 2002 to 2005.  I can work wtih that for a while, or until MySQL 5.0 is more stable and can work with loops.

Thanks for the help, your query did the job.
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.

All Courses

From novice to tech pro — start learning today.