[SOLVED] date from current week
Moderator: General Moderators
[SOLVED] date from current week
Hi,
I need to draw data from a table limited to dates from the current week. I've tried lots of variations using date and time functions from the mysql manual but can't get it to work. Is it possible to just draw out all date from a week if i have the week number?
I need to draw data from a table limited to dates from the current week. I've tried lots of variations using date and time functions from the mysql manual but can't get it to work. Is it possible to just draw out all date from a week if i have the week number?
Last edited by irealms on Tue Oct 19, 2004 6:09 am, edited 3 times in total.
-
kettle_drum
- DevNet Resident
- Posts: 1150
- Joined: Sun Jul 20, 2003 9:25 pm
- Location: West Yorkshire, England
Use mktime() with the start of the week and then with the end of the week and you'll have the start and end of the week in unix epoch which you can then either use like this or transform into a date value and use in an sql query:
Code: Select all
SELECT * FROM blah WHERE time_added BETWEEN '$start_time' AND '$end_time'Code: Select all
SELECT * FROM foo WHERE WEEK(datefield) = '$week';that would fetch data for $week range from any year, not only current one.timvw wrote:Code: Select all
SELECT * FROM foo WHERE WEEK(datefield) = '$week';
I could use this with AND YEAR to make it use currrent year too i think. But how do i get the current week? Would this work?timvw wrote:Code: Select all
SELECT * FROM foo WHERE WEEK(datefield) = '$week';
$week = date('w');
*edit*
Tried this but it's returning "2" as the current week for some reason. Any ideas?
Current code:
Code: Select all
//find current week, even just using date('w'); gave week as "2" as well
//the method of setting the variable below ensures the right year is used
$week = date('w', mktime(0, 0, 0, date("m"), date("d"), date("Y")));
//query
$weekorders = "SELECT * FROM orders WHERE WEEK(dateadded)='$week' AND status!='Quote' AND sub='0'";
$weekordersq = mysql_query($weekorders, $db_conn) or die("Query $weekordersq Failed".mysql_error());in the case you don't have the weeknumber, but need to generate it
Code: Select all
SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());For last week i changed the above code to:timvw wrote:in the case you don't have the weeknumber, but need to generate it
Code: Select all
SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());
Code: Select all
SELECT * FROM bar WHERE YEARWEEK(datefield)=YEARWEEK(NOW());