Link to home
Start Free TrialLog in
Avatar of Robert Granlund
Robert GranlundFlag for United States of America

asked on

Display an entire week from one day

I asked a similar question but thought I would ask it in a different way.

If I have one day, November 29th 2013 and I want to choose every thing in DB table that has that date, but I want to SELECT everything in the database for the week of Nov 29th 2013 how do I do that.  The date will always change so I don't have a start and finish date so I have to use php to determine the entire week containing November 29th
Avatar of Melih SARICA
Melih SARICA
Flag of Türkiye image

select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
Avatar of Robert Granlund

ASKER

@non_zero can you write that using November 29 2013.  I'm still learning php and am a newbie.
declare @requestDate date
set @requestDate= '2013-10-29'

select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
@non_zero, almost there... but what am I doing wrong here?

	$requestDate = '2013-10-29';


	$bg = '#eeeeee'; 
		
	$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
	FROM doctor_doctor, doctor_schedule
	WHERE where datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, $requestDate) AND doctor_schedule.schedule_date='$new_date' AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1' 
	ORDER BY date DESC");

Open in new window

Try something like this...

$alpha = date('c', strtotime('Nov 29, 2013'));
$omega = date('c', strtotime('Nov 29, 2013 + 1 week'));
$query = "SELECT... WHERE important_date BETWEEN '$alpha' AND '$omega'";

Open in new window

What is the result u get from that query..

I guess $newdate must be requestdate

and also between datepart and newdate it must be or not and to get the entire week

$new_date = '2013-10-29';


      $bg = '#eeeeee';
            
      $sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
      FROM doctor_doctor, doctor_schedule
      WHERE (datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, $new_date) or doctor_schedule.schedule_date='$new_date' ) AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1'
      ORDER BY date DESC");
There was a typo in my Query.  However, I get the ERROR:
Message - FUNCTION thedocto_db.datepart does not exist

Can you shed any light on that error?
IS ur Server MSSQL Server or Something else.. Code i send is for MSSQL Server
@RAy, the problem is, it does not take into consideration the entire week.  Only one day that is not the start of the week
@non_zero MySQL

	$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
	FROM doctor_doctor, doctor_schedule
	WHERE datepart(ISOWEEK, doctor_schedule.schedule_date) = datepart(ISOWEEK, '$requestDate') AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1' 
	ORDER BY doctor_schedule.schedule_date DESC");

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Melih SARICA
Melih SARICA
Flag of Türkiye image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
USE YEARWEEK INSTEAD OF DATEPART
This part kind of threw me off...
If I have one day, November 29th 2013 and I want to choose every thing in DB table that has that date, but I want to SELECT everything in the database for the week of Nov 29th 2013 how do I do that.
Why would you want to do that?  Normally, you would either need a results set with a day or a results set with a week.  SELECT is a term of art in software development.  Choose is not.

These sorts of questions are best framed by creating the SSCCE.  For this, you would post the CREATE TABLE statement, along with the information needed to load the test data into the table.  Then you would show us by example, exactly which rows you want to get from the query.  The advantage of doing it this way, instead of trying to describe what you want, is that you will (1) remove any ambiguity in the language and (2) often solve the problem yourself, without having to guess.  It's a great tool/concept and it really makes asking questions fast and easy.