i have a site which i want to generate a weekly report of activity from. the monthly report should be fine as i could set the query to format the date into month and where "date" is NOW() however how can i generate a weekly report in mysql?

any tips would be great.

many thanks

Recommended Answers

All 6 Replies

Use the "week" modificators for grouping on your date field.

SELECT * FROM mytable group by date_format(mydate,'%U') as week

%U is a formatting parameter for the date_format function which displays the week of the input date.
To match only the current week use

WHERE WEEK(somedatefield) = week(now())

thanks alot for your help


i have tried this:

SELECT commission.ProductID, commission.Product, commission.ClientID, commission.ClientFirstName, commission.ClientLastName, commission.UserID, commission.Firstname, commission.Lastname, commission.Amount, commission.NPW, commission.DatePaid
FROM commission
GROUP BY DATE_FORMAT(commission.DatePaid,'%U') as WEEK

but it errors, can you see what i have done wrong?

thanks again

what i am trying to acheive is a weekly report league table. i have a view that shows everything paid, but i would like to be able to view task activity also in this report. what woudl be the best way to query the commission table to show the top user for that week with what they have been paid?

many thanks

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, learning, and sharing knowledge.