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
DatabasesPHPSQL

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon
Melih SARICA

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

@non_zero can you write that using November 29 2013.  I'm still learning php and am a newbie.
Melih SARICA

declare @requestDate date
set @requestDate= '2013-10-29'

select * from TableName where datepart(ISOWEEK,dateColumn) = datepart(ISOWEEK,@requestDate)
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Robert Granlund

@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

Ray Paseur

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

Melih SARICA

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");
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Robert Granlund

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?
Melih SARICA

IS ur Server MSSQL Server or Something else.. Code i send is for MSSQL Server
ASKER
Robert Granlund

@RAy, the problem is, it does not take into consideration the entire week.  Only one day that is not the start of the week
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Robert Granlund

@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
Melih SARICA

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Melih SARICA

USE YEARWEEK INSTEAD OF DATEPART
Ray Paseur

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.