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
LVL 8
rgranlundAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Melih SARICAOwnerCommented:
select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
0
rgranlundAuthor Commented:
@non_zero can you write that using November 29 2013.  I'm still learning php and am a newbie.
0
Melih SARICAOwnerCommented:
declare @requestDate date
set @requestDate= '2013-10-29'

select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

rgranlundAuthor Commented:
@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

0
Ray PaseurCommented:
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

0
Melih SARICAOwnerCommented:
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");
0
rgranlundAuthor Commented:
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?
0
Melih SARICAOwnerCommented:
IS ur Server MSSQL Server or Something else.. Code i send is for MSSQL Server
0
rgranlundAuthor Commented:
@RAy, the problem is, it does not take into consideration the entire week.  Only one day that is not the start of the week
0
rgranlundAuthor Commented:
@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

0
Melih SARICAOwnerCommented:
$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 YEARWEEK(doctor_schedule.schedule_date) =  YEARWEEK('$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");
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Melih SARICAOwnerCommented:
USE YEARWEEK INSTEAD OF DATEPART
0
Ray PaseurCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.