dates
Posted: Tue May 31, 2005 7:44 am
Hi all,
I have this query which bring me the results for a counter weekly and displays the date.
I need for it to start at the start of every month (e.g. 01 May 2005), count the results for every week and then make the last date the end of the month (31st May 2005).
How can i do this:
the display should look like this
25/05/05 - 31/05/05 4802
17/05/05 - 24/05/05 5053
09/05/05 - 16/05/05 4788
01/05/05 - 08/05/05 5000
I have this query which bring me the results for a counter weekly and displays the date.
I need for it to start at the start of every month (e.g. 01 May 2005), count the results for every week and then make the last date the end of the month (31st May 2005).
How can i do this:
Code: Select all
<?php
select DATE_FORMAT(DATE_SUB(h_date, INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS start_of_week, DATE_FORMAT( DATE_SUB( DATE_ADD(h_date, INTERVAL 6 DAY ), INTERVAL WEEKDAY(h_date) DAY), \"%d/%m/%y\" ) AS end_of_week, DATE_FORMAT(h_date, \"%u %M %Y\") AS week, SUM(h_count) AS week_total,e.* FROM tbl_hitcounter e WHERE DATE_FORMAT(h_date, \"%M %Y\") = DATE_FORMAT(NOW(), \"%M %Y\") GROUP BY week ORDER BY week DESC
?>25/05/05 - 31/05/05 4802
17/05/05 - 24/05/05 5053
09/05/05 - 16/05/05 4788
01/05/05 - 08/05/05 5000